How to select id if element contains all data in a list in SQL?

Question

I have a DB with the following schema:

StoreID | BookName
------------------
   1      Harry Potter
   1      Lord of the Rings
   1      Hobbit
   2      Hobbit
   2      Hunger Games
   3      American Gods
   3      Redwall
   4      Calvin and Hobbes

I want get all the store IDs that contain all the books mentioned in the list ['Lord of the Rings', 'Hobbit']

Is there an operator in SQL that can do exactly that?

I know that the IN operator in SQL can obtain all the storeId's that contain at least one of the books in the list. I believe the script would look something like this:

Select StoreID
FROM BookStores
WHERE BookName IN ['Lord of the Rings', 'Hobbit']

Is there a script that can do something similar except only return storeId's if they contain all of the books mentioned in the list? (In this case, the only id returned would be 1)

Answer 1

You can use aggregation:

Select StoreID
FROM BookStores
WHERE BookName IN ('Lord of the Rings', 'Hobbit')
GROUP BY StoreID
HAVING COUNT(*) = 2

This assumes that the same book does not appear twice in the same store. If that may happen, then you can change the HAVING clause to:

HAVING COUNT(DISTINCT BookName) = 2