CODEWITHSUNDEEP
mysql
Sarah
Sarah

Reputation: 754

SQL Query comparing values in different rows

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:

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

  1. Category 4
  2. OR Category 2 but not Category 3 or 4
  3. 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

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 4

user8767616
user8767616

Reputation:

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));

Upvotes: 1

Related Questions