Using SQL to identify instances where there is a specified unique grouping/combination

In Oracle SQL, I am trying to identify instances where a unique combination of records is present. For example, I have table ITEMS that lists items sold by a store chain where Store_ID is the location and the Item_ID is the item:

Store_ID | Item_ID
01       | A
02       | A
02       | B
02       | C
03       | B
04       | A
04       | B
04       | C

…and I want to query the table to identify which store IDs sell all items (the exact combination of ‘A’, ‘B’, and ‘C’). The result of the table above woud be Store_IDs 02 and 04.

I’ve tried the following, but it’s not returning any rows:

SELECT i.Store_ID
FROM Items i
WHERE i.Item_ID = 'A'
AND   i.Item_ID = 'B'
AND   i.Item_ID = 'C'

I’ve also looked at using CONTAINS but can’t seem to get it together. I’m sure this is easy, but I’m stuck.

Any help would be appreciated.

Answer

Do a GROUP BY. Use HAVING to make sure all of A, B and C are there.

SELECT i.Store_ID
FROM Items i
WHERE i.Item_ID in ('A', 'B', 'C')
group by i.Store_ID
having count(distinct i.Item_ID) = 3

Advertisement

Answer