Joining tables when certain one table has no values?

I have these tables:

USER TABLE
uid | name | role
    |      |
1   | bob 开发者_如何学JAVA | 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).

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

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