Page 1 of 1

WHERE statement causing JOIN to leave out data

Posted: Mon Jul 20, 2009 9:43 am
by joshmaker
I'm having a bit of trouble selecting all of the rows that I want from a MySQL query that joins two tables together. Here is the query I start with (that works great):

Code: Select all

SELECT categories.name, color, COUNT(tasks.id)  as num_tasks
FROM categories
LEFT JOIN tasks ON tasks.category_id = categories.id
GROUP BY categories.id
and this is the table that results:

Code: Select all

name     | color  | num_tasks
=============================
Work     | red    | 4
Personal | green  | 1
Vacation | blue   | 0
Other    | Orange | 3
So far so good, as the LEFT JOIN ensure that even categories that currently contain no tasks are included. However, I run into trouble when I want the num_tasks field to include only tasks that have not yet been marked as completed. Here is how I try to do that:

Code: Select all

SELECT categories.name, color, COUNT(tasks.id)  as num_tasks
FROM categories
LEFT JOIN tasks ON tasks.category_id = categories.id
WHERE tasks.done = 0    # New line of code
GROUP BY categories.id
and here is what I get:

Code: Select all

name     | color  | num_tasks
=============================
Work     | red    | 2
Personal | green  | 1
I had thought that because of the LEFT JOIN categories without uncompleted tasks would still show up but would have num_task = 0 like this:

This is what I want:

Code: Select all

name     | color  | num_tasks
=============================
Work     | red    | 2
Personal | green  | 1
Vacation | blue   | 0
Other    | Orange | 0
I can think of ways to get the results that I want that involve sub-queries but I think that will put an unnecessary load on the server. Is there a way for me to get the results that I want by changing the type of JOIN statement? OUTER vs INNER, etc?

Re: WHERE statement causing JOIN to leave out data

Posted: Mon Jul 20, 2009 9:50 am
by Eran
The WHERE clause affects the entire query. If you want only the left join to be affected, you need to include the condition in the ON clause instead of the WHERE:

Code: Select all

LEFT JOIN tasks ON tasks.category_id = categories.id AND tasks.done=0

Re: WHERE statement causing JOIN to leave out data

Posted: Mon Jul 20, 2009 10:15 am
by joshmaker
Of course! I knew there was a simple solution that I was overlooking. Thanks for the help.