SQL Query comparing values in different rows

Question

I have a table structured as follows:

| product_id | category_id |
| 1          | 1           |
| 1          | 2           |
| 2          | 1           |
| 2          | 2           |
| 2          | 3           |
| 3          | 1           |
| 3          | 2           |
| 3          | 3           |
| 3          | 4           |

So:

product 1 is in category 1 and 2
product 2 is in category 1, 2 and 3
product 3 is in category 1, 2, 3 and 4
etc...

How can I structure an SQL Query that can retrieve a list of product_ids that are in:

Category 4
OR Category 2 but not Category 3 or 4
OR Category 1 but not Category 2

I have tried:

SELECT * 
FROM `catalog_category_product`
WHERE category_id = 1
 OR (category_id = 2 AND category_id NOT IN (3,4))
 OR (category_id = 1 AND category_id NOT IN (2,3))
GROUP BY product_id

... but this is not working as hoped.

Thanks very much in advance

Sarah

You need to join the table to itself and check the Category 2 condition on the first instance and the "but not Category 3 or 4" against the second instance of the table. — axiac
– axiac, Commented Oct 19, 2017 at 11:35

David · Accepted Answer · 2017-10-19 11:56:05Z

4

You can use group by and having:

select product_id
from t
group by product_id
having (sum(category_id = 4) > 0) 
        or
       ( sum(category_id = 2) > 0 and
         sum(category_id in (3, 4)) = 0
       ) 
        or 
       ( sum(category_id = 1) > 0 and
         sum(category_id = 2) = 0
       );

The expression sum(category_id = 4) counts the number of rows with category 4 for each product. The > 0 is saying that at least one such row exists. The = 0 is saying that there are no such rows.

edited Oct 19, 2017 at 11:56

David

1,1434 gold badges18 silver badges30 bronze badges

answered Oct 19, 2017 at 11:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Harshil Doshi Over a year ago

Awesome solution!!

Sarah Over a year ago

Thanks, I will try it out, should line 7 read ) or instead of ) and ?

Sarah Over a year ago

(obvious question was obvious, sorry). Works a treat.

user8767616 · Accepted Answer · 2017-10-19 11:40:22Z

1

Try this using the IN predicate:

SELECT DISTINCT product_id
FROM table1
WHERE category_id = 4
   OR (category_id = 2
       AND
       product_id IN(SELECT product_id from table1 WHERE category_id IN(3,4)))
   OR (category_id = 1
      AND
      product_id IN(SELECT product_id from table1 WHERE category_id != 2));

Demo

answered Oct 19, 2017 at 11:40

user8767616

Collectives™ on Stack Overflow

SQL Query comparing values in different rows

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related