Is there a way in sql to use join, but in the results returned, to fetch all rows joined and not joined?

Question

Below is my database structure.

users

id|username|email               |country|datenumber|
--|--------|-----               |-------|----------|
 1|user1   |example@example.com | GB    |2017-11-08| 
 2|user2   |example@example.com | GB    |2017-11-08| 
 3|user3   |example@example.com | GB    |2017-11-08| 
 4|user4   |example@example.com | GB    |2017-11-08| 
 5|user5   |example@example.com | GB    |2017-11-08|

faillogs

id|userid  |type        |datenumber|
--|--------|------------|----------|
 1|1       |wronganswer |2017-11-08| 
 2|3       |wronganswer |2017-11-08| 
 3|3       |wronganswer |2017-11-08| 
 4|3       |wronganswer |2017-11-08| 
 5|5       |wronganswer |2017-11-08| 
 6|5       |wronganswer |2017-11-08| 
 7|5       |wronganswer |2017-11-08| 
 8|5       |wronganswer |2017-11-08| 
 9|5       |wronganswer |2017-11-08|

cashouts

id|userid  |amount      |datenumber|
--|--------|------------|----------|
 1|1       |1.47        |2017-11-08| 
 1|1       |2.97        |2017-11-08| 
 1|2       |1.05        |2017-11-08|

My question is asking, how I can fetch all the users which have joined but also list a count of faillogs.

The below SQL fetches all the users

SELECT * FROM users

The SQL below does a join to fetch the faillogs.

SELECT *, COUNT(faillogs.userid) FROM users    
INNER JOIN faillogs 
ON users.id=faillogs.userid               
GROUP BY faillogs.userid                          
ORDER BY users.datenumber ASC

I would like the benefits of being able to fetch a count of the faillogs for each user with a join WITH the added benefit of showing all users, not just the users that have a faillog. How do I do this?

I have a SQL fiddle here. http://sqlfiddle.com/#!9/56cf4/4

Just change your INNER JOIN to a LEFT OUTER JOIN and you're golden. — JNevill, Nov 08 '17 at 15:42
You GROUP BY usage is invalid SQL. Which DBMS are you using? — , Nov 08 '17 at 15:45
I am using mysql/mariadb. I could use GROUP BY on the sqlfiddle link I gave. I tested it in sqlfiddle and it worked, before putting it in the question. — desbest, Nov 08 '17 at 16:18
I'm new to joins. Time to refer to this classic question. https://stackoverflow.com/q/17946221/337306 — desbest, Nov 08 '17 at 16:19

score 1 · Answer 1 · answered Nov 08 '17 at 15:42

1

you must use LEFT JOIN instead of INNER JOIN

answered Nov 08 '17 at 15:42

Marian Nasry

821
9
22

SQLChao · Accepted Answer · 2017-11-08T15:50:13.903

1

Use LEFT JOIN. Also grouped by users.id instead of faillogs.userid since some of those could/will be NULL.

SELECT users.id, 
       users.username, 
       users.email, 
       users.country, 
       users.datenumber, 
       Count(faillogs.userid) 
FROM   users 
       LEFT JOIN faillogs 
              ON users.id = faillogs.userid 
GROUP  BY users.id, 
          users.username, 
          users.email, 
          users.country, 
          users.datenumber 
ORDER  BY users.datenumber ASC

edited Nov 08 '17 at 15:50

answered Nov 08 '17 at 15:44

SQLChao

7,709
1
17
32

1

(Invalid GROUP BY according to most dbms.) Just saw you copied the original query's select list... – jarlh Nov 08 '17 at 15:45
@jarlh good catch. I was just going purely off of his fiddle and didn't pay attention. – SQLChao Nov 08 '17 at 15:51

Is there a way in sql to use join, but in the results returned, to fetch all rows joined and not joined?

2 Answers2