CODEWITHSUNDEEP
mysqlsql
Grzegorz
Grzegorz

Reputation: 3608

Using AND (OR) to select based on multiple row values

I want to create filter query which uses AND and OR on many to many relation table.

Table consists only of 2 columns id_product and id_category

My query I'm using, but its not working: 

SELECT id_product FROM id_category WHERE ( id_category = 1)  AND (id_category = 2) 
AND ( id_category = 3 OR id_category = 4 OR id_category = 5) GROUP BY id_product

I would like to retrieve products that are in categories at the same time. Only IDs of products which are in category 1 AND 2 AND (3 or 4 or 5)

Upvotes: 0

Views: 55

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

Just for fun, to use a single condition in the HAVING clause:

SELECT id_product
FROM yourTable
WHERE id_category BETWEEN 1 AND 5
GROUP BY id_product
HAVING
    COUNT(DISTINCT CASE WHEN id_category IN (3, 4, 5)
                        THEN 3 ELSE id_category END) = 3;

Demo

The logic here is to first restrict the query to only id_category values (1,2,3,4,5). Then, we assert but first map id_category values of (3,4,5) to the same single value 3. The assertion is that the distinct count of mapped id_category values is 3, which would imply that 1, 2, and (3,4,5) all occur for that product.

Upvotes: 2

forpas
forpas

Reputation: 164069

You must group by id_product and put these conditions in a HAVING clause:

SELECT id_product 
FROM tablename
GROUP BY id_product 
HAVING 
  SUM(id_category = 1) > 0
  AND
  SUM(id_category = 2) > 0
  AND
  SUM(id_category IN (3, 4, 5)) > 0

Upvotes: 3

Related Questions