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)