MySQL count and join

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 sho开发者_开发技巧w 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

---

A simple way to do this is to add an owner role and treat it like any other role. Another way would be to use a UNION.

---

SELECT COUNT(*) FROM tasks_roles_users WHERE user_id = "2"

That will get all tasks that "janedoe" has a roll in.

If there aren't very many rolls (say, less that 8), you might want to save the rolls as constants in your code, instead of making SQL queries for such a small thing.

define('ROLL_COORDINATOR', 1); // just an integer unique from other roll constants

---

SELECT COUNT(*) FROM tasks_roles_users WHERE user_id IN (SELECT id FROM users WHERE username = 'johndoe')
SELECT COUNT(*) FROM tasks_roles_users WHERE user_id IN (SELECT id FROM users WHERE username = 'janedoe')