CODEWITHSUNDEEP
mysqlsqlmultiple-conditions
rokdazone
rokdazone

Reputation: 1

MySQL: Multiple conditions

Using MySQL

What i wan't to achieve is to ONLY return products (WHERE pf.filter = 10 AND pf.filter = 15 AND pf.filter = 18) and no other products.

I thought the below query would work, but it gives me no results. When i remove the WHERE, AND conditions there is 3 rows where pf.filter_id is 10,15,18

SELECT * FROM products p
LEFT JOIN filter pf ON (p.product_id = pf.product_id)
WHERE pf.filter = 10
AND pf.filter = 15
AND pf.filter = 18
GROUP BY p.product_id

How should this SQL Query be constructed to work?

Upvotes: 0

Views: 177

Answers (3)

AlecTMH
AlecTMH

Reputation: 2725

Your problem is the value can't be 10 and 15 and 18 at the same time, try this:

SELECT * FROM products p
LEFT JOIN filter pf ON (p.product_id = pf.product_id)
WHERE pf.filter = 10
OR pf.filter = 15
OR pf.filter = 18
GROUP BY p.product_id

or better

SELECT * FROM products p
LEFT JOIN filter pf ON (p.product_id = pf.product_id)
WHERE pf.filter IN (10, 15, 18)
GROUP BY p.product_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Try using GROUP BY and HAVING to get the products that have all three filters. Then join in the rest of the information:

SELECT p.*
FROM products p JOIN
     (SELECT pf.product_id
      FROM filter pf
      WHERE pf.filter IN (10, 15, 18)
      GROUP BY pf.product_id
      HAVING COUNT(*) = 3
     ) pf
     ON p.product_id = pf.product_id;

Upvotes: 1

Dekel
Dekel

Reputation: 62556

The value of filter can't be 10, 15 and 18 the same time.

You probably want the value to be 10 OR 15 OR 18:

SELECT * FROM products p
LEFT JOIN filter pf ON (p.product_id = pf.product_id)
WHERE pf.filter = 10
OR pf.filter = 15
OR pf.filter = 18
GROUP BY p.product_id

Upvotes: 1

Related Questions