MySQL Left Join + Where is the problem

Question

I have a user table: id, type, name

and article table: id, writer_id, status

where articles.writer_id = users.id.

I would like to display a table of each username WHERE type = 'writer' along with how many articles are associated with them that have status = 'assigned'.

So far, I:

SELECT u.name, COUNT(a.id) as count FROM users u LEFT OUTER JOIN articles a ON a.writer_id = u.id WHERE u.type = 'writer' AND a.status = 'assigned' GROUP BY u.name 

The problem is that this does not display writers with 0-assigned articles associated with them. I'm sure I need a subquery, but I'm not sure what to do. Thanks in advance!

Answer 1

Since you are using a LEFT JOIN , move the predicate a.status = 'assigned' from the WHERE clause to the JOIN clause.

 SELECT u.name, COUNT(a.id) as count FROM users u LEFT OUTER JOIN articles a ON a.writer_id = u.id AND a.status = 'assigned' WHERE u.type = 'writer' GROUP BY u.name 

Explanation: For those users who do not have a.status article, it will be NULL . Leaving the predicate in WHERE, defeats the target of the LEFT connection, since NULL = 'assigned' will be evaluated as false.