Tream Tream - 3 months ago 9
MySQL Question

MySQL-Query to select a object with two exact records

I have follwing table in MySQL:

some_table

user_id | obj_id
-----------------
5 | 1
6 | 1
7 | 2
8 | 2


Now I need a Query, which will get me the obj_id, if this obj_id has both "user_id = 5" and "user_id = 6" (in the example above it is obj_id=1).

Is something like this possible with MySQL?

Answer

One method uses group by and having:

select obj_id
from some_table t
where user_id in (5, 6)
group by obj_id
having count(distinct user_id) = 2;