MySQL JOIN with where子句和group by count

Question

I run a the following query,

SELECT status.status_id, status.status_name, COUNT(workbin.status_id)
FROM `status`
LEFT JOIN `workbin` ON workbin.status_id = status.status_id
GROUP BY status.status_id

and got the output

status_id status_name count
1   New              44
2   On Hold          1
3   In Analysis      2
4   In Development   12
5   In Testing       17
6   In Release       2
7   Completed        151
8   In Review        0
9   Unit Testing     0
11  Rework           0
12  Reopen           0

Now i need to add a where clause in this query, to retrive the data based on the user id in the workbin table. So the result will show only the count for the user. The user id is also stored in the workbin table.

If i add a where clause WHEREworkbin.task_assigned_id= 37 and found few status_id and status_name are missing(which have the 0 value). But i need all the status counts for the task assigned user(workbin.task_assigned_id).

Answer 1

It's because by WHERE clause, you are specifying to show only the records which have a value task_assigned_id = 37 in workbin table. So, you cannot get the rows, for which you don't have a row in workbin table (even though it is in status table.) The solution can be something like this, to keep the conditions of your WHERE clause in LEFT JOIN part:

SELECT status.status_id, status.status_name, COUNT(workbin.status_id)
FROM `status`
LEFT JOIN `workbin` ON workbin.status_id = status.status_id AND workbin.task_assigned_id= 37
GROUP BY status.status_id