sqlt-sqlgroup-bynot-exists

Only show group which matches at least one condition

If I have a chain of stores for example and I want to find out the list of stores who specifically sell either Apples, Bananas or Pears but not other items, how would that be coded in SQL?

So if there is a store which has Apples, Bananas, Pears AND Melons, then they will not be in the list because they strictly need to have the 3 mentioned items, some of them, but never additional items which are not in the list.

Current Table:

enter image description here

Expected Outcome:

Store 1
Store 2
Store 3

Store 4 (group) will be removed because it contains an item which is not in the accepted fruit list.

Current Code:

SELECT DISTINCT ss.shopName
FROM sl.shop_stores ss
INNER JOIN sl.items i ON i.id = ss.item_id
WHERE i.item_name NOT IN ('Apple','Pear', 'Banana')

This will just remove the row rather than the entire shop if it contains other items outside of the accepted fruits.

Pseudo Code:

SELECT ALL SHOPS 
WHERE ITEMS
ONLY IN (APPLE, PEAR, BANANA)
Solution

  • One method would be to use a COUNT(DISTINCT) on the item in the HAVING to check that the returned value has the same number a conditional COUNT(DISTINCT) on the items you want:

    SELECT ss.shopName
FROM sl.shop_stores ss
     INNER JOIN sl.items i ON i.id = ss.item_id
GROUP BY ss.shopName
HAVING COUNT(DISTINCT i.item_name) = COUNT(DISTINCT CASE WHEN i.item_name IN ('Apple','Pear', 'Banana') THEN i.item_name END);

    But it would probably be easier/more performant to just do an EXISTS:

    SELECT ss.ShopName
FROM sl.shop_stores ss
WHERE NOT EXISTS (SELECT 1
                  FROM sl.items i
                  WHERE i.id = ss.item_id
                    AND i.item_name NOT IN ('Apple','Pear', 'Banana'));

    Note that the above will return shops that stock no items too, though I would hope that isn't something you have; a shop that sells nothing isn't really a "shop".