The link table. The selection of the values object_id, which option_id equal to multiple values

There is a table of relationships:

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 are object_id, option_id which is equal to multiple values.

Example:
object_id option_id
1 1
1 2
1 3
2 1
3 3

Need to select object_id, option_id which is equal to 1 and 2.

How to do it in MySQL? ;)

PS At first glance, it's very simple, but for some reason it does not work...
October 8th 19 at 00:34
5 answers
October 8th 19 at 00:36
SELECT object_id FROM object_option WHERE option_id IN (1, 2)
hmm, missed the key word "both" =) - Velva_Wym commented on October 8th 19 at 00:39
October 8th 19 at 00:38
First, how can there be two primary key?

Secondly, how many were not looking for the trick, not found:
select * from `object_option` where `object_id` in(1, 2) and `option_id` in(1, 2);
Well, the logical "or", if you want to collect all values where object_option is 1 or 2 or option_id is 1 or 2. - Velva_Wym commented on October 8th 19 at 00:41
First, how can there be two primary key?

Here one PC. He composite.
what I was not looking trick, not found:

The catch is that your query will return those objects for which there is only one of the two properties. The author needs to return objects that have both properties. - Florine_Yundt commented on October 8th 19 at 00:44
October 8th 19 at 00:40
SELECt fields FROM table WHERE option_id IN (1,2) UNION SELECT fields FROM table WHERE object_id IN (1,2)

This approach will allow you to use indexes (if they exist) instead of iterating through all table rows (OR)
Or I not so understood a question? - Velva_Wym commented on October 8th 19 at 00:43
Yes, you don't really understand the question...
>You need to select from the table are object_id, option_id which is equal to multiple values. - Florine_Yundt commented on October 8th 19 at 00:46
October 8th 19 at 00:42
>You need to select from the table are object_id, option_id which is equal to multiple values.

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
October 8th 19 at 00:44
Now formed a similar task. You do not accidentally found a solution?
My head came only to process the request using the tempo function:

SELECT object_id, GROUP_CONCAT(option_id) AS temp FROM object_option WHERE option_id IN (1, 2) GROUP BY object_id HAVING temp = '1,2'


It turns out that we combine in a group all options of the object and get the temp variable. Next, compare this variable with a set of options. If You have found a more delicate solution, then please share ))
SELECT `object_id`, COUNT(`option_id`) AS `option_count` FROM `object_option` WHERE option_id IN (1, 2) GROUP BY `object_id` HAVING `option_count` = 2

HAVING `option_count` = the number of options passed in - Velva_Wym commented on October 8th 19 at 00:47
: Thanks! As an option )) I Really this solution is not particularly suitable, because I'm doing a query to several tables, and from table object_option I need to pull ALL the option_id of the objects in this list will be all the option_id values. If option_id is 10, and I will look for only 3 values, then the condition is not already executing. - Florine_Yundt commented on October 8th 19 at 00:50

Find more questions by tags SQLMySQL