MySQL Select distinct rows with same id with different value in a column and excluded a specific value in another column

I have a MySQL database table like following:

id   task_id     users
1       1          1
1       2          2
2       1          2
2       2          1
2      10          1
3       1          2
3       2          2
3       3          2
4       1          2

I want to select id’s who have multiple users. In this condition users 1 and 2 are valid. But the problem is, along with above condition I also wanted to select those who do not have a task_id = 10. So final output should be id = 1.

I achieved first condition fulfilled by the help of this post.

But could not achieved result excluding task_id = 10 because trying with following query, it is still showing both users 1 and 2.

select distinct t.id
from table as t
join (
    select id
    from table
    group by id
    having count(distinct users) > 1
) as t2
on t.id = t2.id
AND task_id != 10

Answer

Just use aggregation and having;

select id
from t
group by t
having count(distinct user) > 1 and
       sum(task_id = 10) = 0;

If you wanted the original rows, I would express this using exists:

select t.*
from t
where not exists (select 1 from t t2 where t2.id = t.id and t2.task_id = 10) and
      exists (select 1 from t t2 where t2.id = t.id and t2.user <> t.user);

Advertisement

Answer