How to write mysql query?

Question

MySQL

There is a table with product_id and filter-value id. The site has a filter. When filtering, we get an array of id filter-values.
How to get all product_ids that have all value filter ids.

For example, how to get the product_id, where the av_id column contains the values ​​3,11,15. In the case like in the picture, product_id = 87 will be returned.
And if there are values ​​of 2.3, then product_id = 87 and product_id = 305 will be returned.

Or maybe there is a more optimal structure?

Answer 1

3 times exists
or 3 times join
look at the plan, choose the optimal one

Answer 2

in the case of all id - you can How to make a SQL query on three tables?

Answer 3

SELECT product_id FROM 
(
SELECT product_id FROM T WHERE av_id = 3
UNION ALL
SELECT product_id FROM T WHERE av_id = 11
UNION ALL
SELECT product_id FROM T WHERE av_id = 15
) AS F
GROUP BY product_id
HAVING COUNT(*) = 3

SELECT product_id FROM 
(
SELECT product_id FROM T WHERE av_id = 3
UNION ALL
SELECT product_id FROM T WHERE av_id = 2
) AS F
GROUP BY product_id
HAVING COUNT(*) = 2

Answer 4

SELECT product_id
FROM table_name
WHERE av_id IN (3, 11, 15)
GROUP BY product_id 
HAVING COUNT(DISTINCT av_id) = 3