Consider the following schema:
Suppliers (sid: integer, sname: string, address: string)
, Parts (pid: integer, pname: string, color: string)
, and Catalog (sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by suppliers.
Find the sids of suppliers who supply only red parts.
So I know I need to eventually select the catalog sid for the supplier but I don't know how to remove all instances of a supplier sid if they sell and item that isn't colored red.
Would appreciate any direction. I have been trying to do combinations of AND but can't seem to eliminate all sids that sell parts not red.
Suppliers who sell only red parts:
Select distinct c.Sid From Catalog c join Parts p on p.pid = c.pid Where p.Color = 'RED' and Not exists (Select * from Catalog Where sid = c.Sid and pid in (Select Pid from Parts Where Color != 'RED'))
In English, it's pretty much exactly what you stated in the question
"Find the sids of suppliers who supply only red parts."
Whichconverting the word
only into it's two parts.... is equivalent to
"Find the sids of suppliers who supply red parts and do not supply any parts that are not red."
Select distinct c.Sid -- "Find the sids of suppliers ..." From Catalog c join Parts p on p.pid = c.pid Where p.Color = 'RED' -- "who supply red parts ... " and Not exists -- " and do not supply ..." (Select * from Catalog -- " Parts ... " Where sid = c.Sid and pid in (Select Pid from Parts -- " ... That are ..." Where Color != 'RED')) -- " ... Not Red"