Here is my schema:
Suppliers(sid: integer, sname: string, address string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
bold indicates primary key.
I want to write a query to find all suppliers who supply every part. Here are two queries I have already:
-- get all parts for a given supplier
SELECT Parts.pid
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Suppliers.sid = 4;
-- gets all parts that exist
SELECT Parts.pid
FROM Parts
What I want to do, in imperative terms, is something like this:
Define result set
Foreach Supplier:
If the list of parts produced by a supplier
is equal to the total list of parts, add this supplier to the result set
Return result set
How can I translate this into MySQL?