Link table. Selecting object_id values ​​whose option_id is equal to several values ​​at the same time

Question

MySQL

There is a link table:

CREATE TABLE `object_option` (
`object_id` int(11) NOT NULL,
`option_id` int(11) NOT NULL
PRIMARY KEY (`object_id`,`option_id`)
)

You need to select from the table such object_id, option_id of which are equal to several values ​​at the same time.

Example:

object_id	option_id
one	one
one	2
one	3
2	one
3	3

You need to select object_id whose option_id is 1 and 2.

How to do it in MySQL? ;)

PS At first glance, everything is very simple, but for some reason it does not work ...

Answer 1

SELECT object_id FROM object_option WHERE option_id IN (1, 2)

Answer 2

First, how can there be two primary keys?
Secondly, no matter how much I searched for a catch, I did not find it:
select * from `object_option` where `object_id` in(1, 2) and `option_id` in(1, 2);

Answer 3

SELECT fields FROM table WHERE option_id IN (1,2) UNION SELECT fields FROM table WHERE object_id IN (1,2)
This approach allows you to use indexes (if any) instead of iterating over all table rows (when using OR)

Answer 4

>It is necessary to select such object_id from the table, option_id of which are equal to several values ​​at the same time.
select o.object_id as object_id
from object_option o
INNER JOIN object_option o2 on o2.object_id = o.object_id
WHERE o2.option_id != o.option_id
GROUP BY o.object_id

Answer 5

Now there is a similar issue. Did you happen to find a solution?
It occurred to me only to process the request through the tempo function:
It turns out that we combine all the options of the object into a group and get the temp variable. Next, we compare this variable with the set of options we need. If you have found a more delicate solution, then please share))