WHERE statement causing JOIN to leave out data
Posted: Mon Jul 20, 2009 9:43 am
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):
and this is the table that results:
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:
and here is what I get:
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:
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?
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.idCode: Select all
name | color | num_tasks
=============================
Work | red | 4
Personal | green | 1
Vacation | blue | 0
Other | Orange | 3Code: 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.idCode: Select all
name | color | num_tasks
=============================
Work | red | 2
Personal | green | 1This is what I want:
Code: Select all
name | color | num_tasks
=============================
Work | red | 2
Personal | green | 1
Vacation | blue | 0
Other | Orange | 0