WHERE statement causing JOIN to leave out data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
joshmaker
Forum Commoner
Posts: 25
Joined: Mon May 15, 2006 2:53 pm
Location: Arlington VA

WHERE statement causing JOIN to leave out data

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: WHERE statement causing JOIN to leave out data

Post 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
joshmaker
Forum Commoner
Posts: 25
Joined: Mon May 15, 2006 2:53 pm
Location: Arlington VA

Re: WHERE statement causing JOIN to leave out data

Post by joshmaker »

Of course! I knew there was a simple solution that I was overlooking. Thanks for the help.
Post Reply