I'm building a tasks system wich has 4 tables:
tasks
id | user_id | end_date
-------------------------
2 | 1 | 2011-02-10
users
id | username
--------------
1 | johndoe
--------------
2 | janedoe
roles
id | role_name
--------------
1 | coordinator
and tasks_roles_users
id | task_id | user_id | role_id
---------------------------------
1 | 2 | 2 | 1
Each task has a creator (ie: johndoe is the owner of task #2), and each task has several users with different roles on that task, in my example "janedoe" is the task #2 coordinator. I'm stuck trying to show to "janedoe" and "johndoe" how many due tasks they have, and I'm having this problem since "johndoe" hasn't a role in the task, he's just the task owner. So how can I tell to both they have 1 task due?
You can accomplish this by doing a LEFT JOIN
SELECT u.id, u.username,
IFNULL(t.Cnt,0) OwnCount,
IFNULL(tr.Cnt,0) RoleCount
IFNULL(t.Cnt,0) + IFNULL(tr.Cnt,0) TotalCount
FROM users u LEFT JOIN (
SELECT user_id, COUNT(*) cnt
FROM tasks
GROUP BY user_id
) t ON u.id = t.user_id
LEFT JOIN (
SELECT user_id, COUNT(*) cnt
FROM tasks_roles_users
GROUP BY user_id
) tr ON u.id = tr.user_id
WHERE t.user_id IS NOT NULL OR tr.user_id IS NOT NULL