How to SQL query elements which have more concrete variation of another attribute?

user_3292988 0
Viewed 41

A short example:

I need to query Store_IDs which have Product_ID 'product1' and 'product2'.

TableName:
Storage_items
    
+----+-------------+----------+
| ID | Product_ID  | Store_ID |
+----+-------------+----------+
|  1 | product1    | store1   |
|  2 | product2    | store1   |
|  3 | product1    | store2   |
|  4 | product3    | store2   |
|  5 | product2    | store3   |
|  6 | product3    | store3   |
+----+-------------+----------+
sql
3 Answers

If you want Store_IDs which have 'product1' and 'product2' and maybe other Product_IDs too:

SELECT Store_ID 
FROM Storage_items
WHERE Product_ID IN ('product1', 'product2')
GROUP BY Store_ID
HAVING COUNT(DISTINCT Product_ID) = 2

If you want Store_IDs which have 'product1' and 'product2' only:

SELECT Store_ID 
FROM Storage_items
GROUP BY Store_ID
HAVING COUNT(DISTINCT Product_ID) = 2
   AND SUM(CASE WHEN Product_ID NOT IN ('product1', 'product2') THEN 1 ELSE 0 END) = 0
user_104988280

A simple WHERE statement should do the trick, I guess.

SELECT Store_ID
FROM Storage_items
WHERE Product_ID = "product1"
OR Product_ID = "product2"
user_92316710

You can use a simple and:

SELECT Store_ID
FROM Storage_Items
WHERE Product_ID = 'product1'
AND Product_ID = 'product2'
user_67092620
Related