Problems counting joined rows with conditional

Question

I am having some problems with MySQL and selecting column count from a joined table. I have a feeling this is going to require a sub-select statement to fetch the rows I would like to join instead of doing it in my primary where condiutional.

I am trying to select a list of project which are linked to a certain user. I would also like to include a count of tasks which are assigned to this project and this user (but only those of a certain status).

I have it working almost - which means not at all. It will only return project id 1, and not id 2. This is because of my 'tasks.status<9' where clause.

Any help would be great. Let me know if I need to explain anything else.

+-------------------------+
| projects_to_users       |
+-----------+-------------+
+ user_id   | project_id  |
+-----------+-------------+
+ 1         | 1           |
+-----------+-------------+
+ 1         | 2           |
+-----------+-------------+


+-------------------------+
| projects                |
+--------------+----------+
+ project_id   | name     |
+--------------+----------+
+ 1            | Foo      |
+--------------+----------+
+ 2            | Bar      |
+--------------+----------+

+------------------------------------------------+
| tasks                                          |
+-----------+--------------+----------+----------+
+ task_id   | project_id   | status   | name     |
+-----------+--------------+----------+----------+
+ 1         | 1            | 1        | Do it    |
+-----------+--------------+----------+----------+
+ 2         | 1            | 1        | Do itt   |
+-----------+--------------+----------+----------+
+ 3         | 1            | 9        | Do not   |
+-----------+--------------+----------+----------+



SELECT count( tasks.task_id ) AS task_count, projects.*
FROM (projects)
LEFT JOIN tasks ON tasks.project_id = projects.project_id
LEFT JOIN projects_to_users ON projects.project_id=projects_to_users.project_id
WHERE tasks.status<9
AND tasks.assigned_user_id = '1'
AND projects_to_users.user_id = '1'
GROUP BY projects.project_id


RETURNS:

+--------------+--------------+--------+
+ task_count   | project_id   | name   |
+--------------+--------------+--------+
+ 2            | 1            | Foo    |
+--------------+--------------+--------+

SHOULD RETURN:

+--------------+--------------+--------+
+ task_count   | project_id   | name   |
+--------------+--------------+--------+
+ 2            | 1            | Foo    |
+--------------+--------------+--------+
+ 0            | 2            | Bar    |
+--------------+--------------+--------+

Answer 1

Why can't you do a simple join like this:

select count(*) from projects_to_users inner join tasks on projects_to_users.project_id=tasks.project_id;

Answer 2

If you want to show per-project, per-user task counts, you should also group by the projects_to_users.user_id field.

Also, the reason why you're not seeing the zero-count project is because your WHERE condition expects there to have been a match by means of the LEFT JOIN clauses. If you move the WHERE criteria into the joins themselves, this should work better for you.

For example:

SELECT count( tasks.task_id ) AS task_count, 
       projects.*
FROM (projects)
LEFT JOIN tasks ON tasks.project_id = projects.project_id AND tasks.status < 9 AND tasks.assigned_user_id = '1'
LEFT JOIN projects_to_users ON projects.project_id=projects_to_users.project_id AND projects_to_users.user_id = '1'
GROUP BY projects.project_id