Selecting records that have multiple values ​​at the same time

Question:

There is a table

+----------------+------------------------+
| item_option_id | select_option_value_id |
+----------------+------------------------+
|            164 |                     10 |
|            168 |                     16 |
|            168 |                     18 |
+----------------+------------------------+

You need to select item_option_id for which select_option_value_id is both 16 and 18…

Use in an OR query, i.e.

select_option_value_id = 16 OR select_option_value_id = 18

or

select_option_value_id IN (16, 18)

not an option, because if select_option_value_id is only 16, then item_option_id will still be selected.

It is not possible to use AND , because when searching by condition

select_option_value_id = 16 AND select_option_value_id = 18

MySQL, having found a row with select_option_value_id = 16 , discards all the rest and the condition fails, since there is no row where select_option_value_id = 18 .

The current request is:

SELECT item_option_id
FROM items_select_options_values
WHERE select_option_value_id IN (16,18)
GROUP BY item_option_id;

Solution:

SELECT item_option_id
FROM items_select_options_values
WHERE select_option_value_id IN (16,18)
GROUP BY item_option_id
HAVING COUNT(item_option_id) = {{количество значений, в данном случае 2}};

Answer:

In general, I got something like this (it seems to work):

SELECT
    TT.item_option_id
FROM
    TestTable TT
INNER JOIN
(
    SELECT
        item_option_id
        ,COUNT(*) Cnt
    FROM
        TestTable
    GROUP BY
        item_option_id
) X ON X.item_option_id= TT.item_option_id
WHERE
    select_option_value_id IN (16, 18)
GROUP BY
    TT.item_option_id
HAVING
    COUNT(*) = MAX(X.Cnt)
Post Views: 5
Scroll to Top