Pep Ag Pep Ag - 3 months ago 8
MySQL Question

Mysql Query issue with join and filter

I have a table which store's Product ID with filter ID. Mapping is one to many. (One product can be associated with many filters). Now i need to fetch records which matches all criteria. Say for example, I need to fetch products which are having FILTERS 1,5 and 7. I want exact match like products which is mapped to 1 AND 5 AND 7.

Below are the table structure

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 have tried below mentioned query but it is not fetching me 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


Kindly help me in resolving this issue

Answer

There may be possible better way to do this, you could use below script as well

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)
Comments