CODEWITHSUNDEEP
mysqlsql
Cobain
Cobain

Reputation: 195

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?

Upvotes: 0

Views: 89

Answers (2)

Kickstart
Kickstart

Reputation: 21513

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)

Upvotes: 1

Faizan Ali
Faizan Ali

Reputation: 13

how about creating a Product column in Store table so that all you have to do is search for Unilever Product in the Store table. Then apply this:

SELECT Product FROM Store WHERE Product = "Unilever"

But of course you wouldnt want the column Product in your Store table so my answer cant be correct then

Upvotes: 0

Related Questions