Cobain Cobain - 11 days ago 6
MySQL Question

MySQL: check all values in a subset are in another table and output corresponding IDs

I have 3 tables:

Store(sID, name, address, mID)
Sells(sID, pID)
Product(pID, name, manufacturer, price)


I need to find which stores stock every product from a given manufacturer. For example: to search Unilever, I'd expect to return only stores which stock ALL Unilever products listed in Product, not just some of them.

I've tried lots of different queries with most being completely off the mark.

Am I right in thinking I need to create a subset of all the products made by Unilever, then somehow go through Sells and check that the list of pIDs for each sID contain all of those in the initial subset? I can then join the result with Store to get the store details.

If that's the correct logic, where would one begin?

This creates a subset of all the unilever products:

SELECT pID FROM Product WHERE manufacturer = "Unilever"


How would I then check this list against each store in Sells to find the ones that contain all the products in the list?

Answer

One possible way is to join the product table twice, once via the sells table and once directly, then use COUNT(DISTINT ...) on each joined table to check they match

SELECT st.*
FROM store st
INNER JOIN sells se ON st.sID = se.sID
INNER JOIN product pr1 ON se.pID = pr1.pID AND pr1.manufacturer = "Unilever"
INNER JOIN product pr2 ON pr2.manufacturer = "Unilever"
GROUP BY st.sID, 
        st.name, 
        st.address, 
        st.mID
HAVING COUNT(DISTINCT pr1.pID) = COUNT(DISTINCT pr2.pID)