can you help me out again ?
Got 2 tables! here:
Products
ID | Name
1 X Product
2 Y Product
3 Z Product
Filters
ID | Name
1 X Filter
2 Y Filter
3 Z Filter
Product_Filter
Product_ID | Filter_ID
1 1
1 2
2 1
2 2
2 3
3 3
This pivot table contains:
X Product has X Filter and Y Filter
Y Product has X Filter and and Y Filter and Z Filter
Z Product has Z Filter
I have this sql code which I can get X and Y Product by defining X Filter's ID.
SELECT DISTINCT `products`.*
FROM `products`
JOIN `filters` ON `products`.`id` = `filters`.`id`
WHERE `product_filter`.`filter_id` = 1;
What if I want to get products only contains X Filter and Z Filter ? like putting and clause like this but it doesn't work of course.:
WHERE `product_filter`.`filter_id` = 1
and 'product_filter'.'filter_id' = 69;
You need to use group by and having count while matching 2 criteria at the same time on a single column something as
select
p.* from Products p
join Product_Filter pf on pf.Product_ID = p.ID
join Filters f on f.ID = pf.Filter_ID
where pf.filter_id in (1,69)
group by p.ID
having count(*) = 2