Select from many-to-many table

Question

I have next table called option_to_name data:

+-----------+---------+
| option_id | name_id |
+-----------+---------+
| 1         | 1       |
| 1         | 2       |
| 2         | 2       |
| 3         | 2       |
+-----------+---------+

I need to select name_id which has relation with 1 and 2 values in option_id. I've tried to use WHERE IN but it works like has 1 or has 2.

SELECT name_id FROM option_to_name WHERE option_id IN (1, 2)

Is there any way to select name_id which has both 1 and 2 values in option_id?

Answer 1

You are close. Just add some aggregation:

SELECT name_id
FROM option_to_name
WHERE option_id IN (1, 2)
GROUP BY name_id
HAVING COUNT(DISTINCT option_id) = 2;