CODEWITHSUNDEEP
mysqlsql-match-all
Mauro
Mauro

Reputation: 1487

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!!

Upvotes: 1

Views: 109

Answers (2)

Glenn
Glenn

Reputation: 156

This seems like over kill but...

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)
INNER JOIN settings s2 ON (s.id = p2.setting_id)
WHERE 
    s.id = 1
    AND s2.id = 2

Upvotes: 0

ajreal
ajreal

Reputation: 47311

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

Upvotes: 2

Related Questions