Search code examples
mysqlsql-match-all

Select a product from filters

I have a problem with a query:

I have 3 tables: products (id, name) settings (id, name) product_setting (product_id, setting_id)

for example: I would like to select only the products you have selected filters!

I do this:

SELECT p. *, s.id as setting
FROM Products p
INNER JOIN product_setting p2 ON (p.id = p2.product_id)
INNER JOIN settings s ON (s.id = p2.setting_id)
WHERE s.id IN (1,2)

but I get all products that have the 'setting' id = 1 OR id = 2. How to get only those products that have those 'setting' (AND)?

thanks!!

Solution 
  • SELECT p.*, s.id as setting
FROM Products p
INNER JOIN product_setting p2 ON (p.id = p2.product_id)
INNER JOIN settings s ON (s.id = p2.setting_id)
WHERE s.id IN (1,2)
GROUP BY p.id
HAVING COUNT(*)=2; // size of IN()