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

Question

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)

we need to see the rest of the query because you will likely have to either do multiple joins to the same table or a subquery — Joe Phillips
– Joe Phillips, Commented May 20, 2019 at 11:49
id_category cannot be at the same time 1 and 2.. So seems natural that a WHERE ( id_category = 1) AND ( id_category = 2) [..etc] will always return an empty result — Kaddath
– Kaddath, Commented May 20, 2019 at 11:50
Its a many to many relation as noted in question :) So id_product is not unique. — Grzegorz
– Grzegorz, Commented May 20, 2019 at 12:53

forpas · Accepted Answer · 2019-05-20 11:52:20Z

3

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

answered May 20, 2019 at 11:52

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Biegeleisen · Accepted Answer · 2019-05-20 12:03:18Z

2

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.

edited May 20, 2019 at 12:03

answered May 20, 2019 at 11:57

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

2 Comments

Grzegorz Over a year ago

Thank you for answer. great to know, but other one is easier to dynamically build and "seems" faster.

Tim Biegeleisen Over a year ago

@Grzegorz Yes, I would probably use the other version in a production code base as well. I only gave this answer just to show that we can write a more terse query with a little SQL magic ^ ^.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Demo

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related