Mysql query issue with connection and filter

Question

I have a table that stores the product identifier with the filter identifier. Matching is one for many. (One product may be associated with many filters). Now I need to get records matching all criteria . Say, for example, I need to get products that have FILTERS 1,5 and 7. I want the exact match to match products that match 1 AND 5 AND 7.

The table structure is shown below.

CREATE TABLE IF NOT EXISTS `product_to_filter` (
  `product_id` int(11) NOT NULL,
  `filter_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`,`filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I tried the query mentioned below, but did not get the desired result.

select distinct p1.product_id, p1.filter_id from product_to_filter p1
join product_to_filter p2 on 1=1
join product_to_filter p3 on 1=1
where
p1.filter_id=1 AND p2.filter_id=5 AND p3.filter_id=7

Please help me in solving this problem.

Answer 1

, script,

SELECT distinct p1.product_id
FROM product_to_filter p1
WHERE EXISTS(SELECT 1 from product_to_filter p2 WHERE p1.product_id=p2.product_id AND p2.filter_id=2) 
AND EXISTS(SELECT 1 from product_to_filter p3 WHERE p1.product_id=p3.product_id AND p3.filter_id=5)  
AND EXISTS(SELECT 1 from product_to_filter p4 WHERE p1.product_id=p4.product_id AND p4.filter_id=7)

Answer 2

JOIN:

SELECT p1.product_id 
FROM product_to_filter p1
JOIN product_to_filter p2 ON p2.product_id = p1.product_id 
JOIN product_to_filter p3 ON p3.product_id = p1.product_id 
WHERE p1.filter_id = 1 
AND p2.filter_id = 5
AND p3.filter_id = 7

Answer 3

Use EXISTSin conjunction with counting all the filters in a sentence HAVING, so there 2, 5, 7should be 3 different filters in the set .

SELECT DISTINCT p1.product_id, p1.filter_id 
FROM product_to_filter p1
WHERE EXISTS (
  SELECT 1
  FROM product_to_filter p2 
  WHERE p1.product_id = p2.product_id 
    AND p2.filter_id IN (2, 5, 7)
  GROUP BY p2.product_id
  HAVING COUNT(DISTINCT p2.filter_id) = 3
  )