Selecting with Multiple OR and AND on same column

Question

I looked over this at many sources but can't really get the solution, It's just that i want to select rows from a table on applying various AND and OR.

I have one table

ID    product_list_id    tag       tag_type    prod_categ
---   ---------------    ---       --------    ----------
66    19                 maroon    color       5
67    19                 printed   design      5
68    19                 cotton    fabric      5
69    20                 blue      color       5
70    20                 printed   design      5

etc...

I need here to get those product_list_id which are both maroon or blue, and printed (there could be multiple combinations of AND and OR)

basic query which i thought would work

select DISTINCT product_list_id 
FROM product_tags 
WHERE (tag = 'blue' OR tag = 'maroon') 
AND tag = 'printed'

but it returns nothing.

Expected Output

product_list_id
19
20

since, the product_list_id 19 and 20 is both printed and is either maroon or blue

P.S. I came through this post, but i need to include even some OR conditions in my result?

Thanks in advance for any help

What is your expected output here? And also please include the logic behind that output. — Tim Biegeleisen, Feb 17 '18 at 08:30
@TimBiegeleisen, Please see the updated question with the expected output — Aman Kumar, Feb 17 '18 at 08:34

Tim Biegeleisen · Accepted Answer · 2018-02-17T09:02:01.977

2

One simple approach is to just aggregate by product_list_id and then assert the requirements:

SELECT product_list_id
FROM product_tags
GROUP BY product_list_id
HAVING
    SUM(CASE WHEN tag = 'printed' THEN 1 ELSE 0 END) > 0 AND
    SUM(CASE WHEN tag IN ('maroon', 'blue') THEN 1 ELSE 0 END) > 0;

We could also approach this using a self-join:

SELECT DISTINCT t1.product_list_id
FROM product_tags t1
INNER JOIN product_tags t2
    ON t1.product_list_id = t2.product_list_id AND
       t1.tag = 'printed' AND
       t2.tag IN ('maroon', 'blue');

Demo

edited Feb 17 '18 at 09:02

answered Feb 17 '18 at 08:37

Tim Biegeleisen

502,043
27
286
360

Thanks a lot @TimBiegeleisen – Aman Kumar Feb 17 '18 at 08:44
@TimBiegeleisen . . . The second query does an `OR` not and `AND` between the conditions. Something that is just "maroon" will be in the result set. – Gordon Linoff Feb 17 '18 at 12:34
@GordonLinoff This is the intended logic, if I read the question correctly. – Tim Biegeleisen Feb 17 '18 at 12:48
@TimBiegeleisen . . . If that is the case, then your first query is wrong and the overall answer is overly complicated (a simple `in` would suffice). – Gordon Linoff Feb 17 '18 at 13:11

Selecting with Multiple OR and AND on same column

1 Answers1

Demo