Filter group if one entry has specific value-CodePudding

I have a table of tasks:

task_id | done_by_user_id
-------------------------
1       | 1
1       | 2
2       | 1
2       | 3
3       | 3
4       | 1

The table contains data which task has be solved by which user, whereas each task can be solved by more than one user. I already have a query that groups the tasks and counts how many users have worked on each task: SELECT task_id, COUNT(*) FROM tasks GROUP BY task_id

Next, I want to filter all groups by user: When one task was solved by one user, I want that task to be removed from my results. What I first tried was SELECT task_id, COUNT(*) FROM tasks WHERE done_by_user_id != ? GROUP BY task_id but that only decreases the count but does not filter the whole group.

CodePudding user response：

SELECT task_id, COUNT(*) 
FROM tasks 
GROUP BY task_id
HAVING NOT SUM(done_by_user_id = ?)

SUM(done_by_user_id = ?) calculates the amount of rows for current task_id and provided user.

NOT allows to return only those rows where this SUM is zero.

CodePudding user response：

I'd do it this way:

SELECT t.task_id, COUNT(*)
FROM tasks AS t
LEFT OUTER JOIN tasks AS u
 ON t.task_id=u.task_id AND u.done_by_user_id=?
WHERE u.task_id IS NULL
GROUP BY t.task_id;

Explanation: try to match each row t to another row u referencing the same task and the specific user. If no match exists, then the outer join will treat the columns of u as NULL. You want those cases, so where u.* is NULL, those are the groups you want to keep.