Joining tables when certain one table has no values?

Question

I have these tables:

USER TABLE
uid | name | role
    |      |
1   | bob  | package 1
2   | jill | package 2
3   | pam  | package 1

NODE TABLE
nid | uid | type
    |     |
1   | 1   | car
2   | 1   | car
3   | 1   | car
4   | 2   | page
5   | 1   | car
6   | 3   | car

If I do:

select u.uid, u.name, count(nid) as totalNodes from USER as u left join NODE on n.uid = u.uid where n.type = 'car' group by u.uid

I end up with:

uid | name | totalNodes
    |      | 
1   | bob  | 4
3   | pam  | 1

In other words, Jill is excluded. Why? And how can I avoid this? I.e. I want Jill to also appear in the list, but with totalNodes as 0 (or even NULL).

Answer 1

use RIGHT JOIN instead left,

try :

select u.uid, u.name, count(nid) as totalNodes from USER as u
right join NODE on n.uid = u.uid where n.type IS NULL or n.type = 'car' group by n.uid

see this excellent post a visual explanation of joins :

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

mysql syntax of join :

http://dev.mysql.com/doc/refman/5.0/en/join.html

Answer 2

You need to perform your aggregate before attempting to join the tables as what you are currently doing is left joining, then restricting the data (at which point Jill is excluded) then grouping. If you do the count and restriction in a subquery you can then left join these results to the user table for the output you want:

SELECT u.uid, u.name, IFNULL(c.nodecount,0) AS `count`
FROM USER u LEFT JOIN (
    SELECT uid, `type` , COUNT(nid) AS nodecount
    FROM node
    WHERE TYPE = 'car'
    GROUP BY uid, type
) AS c ON u.uid = c.uid