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?

Accepted Answer

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
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki