Good evening folks!
Following Problem, I have three tables:
Create TABLE Suppliers ( SID integer, sname varchar(50), adress varchar(50), PRIMARY KEY (SID) ) Create TABLE Parts ( PID integer, pname varchar(50), color varchar(50), PRIMARY KEY (PID) ) Create TABLE Catalog ( SID integer, PID Integer, costreal integer, PRIMARY KEY (PID,SID) )
The question i need to answer is:
“Find the ids of suppliers who supply some red part and some green part.”
I hope you can help me!
Advertisement
Answer
This sounds like aggregation and filtering with having
:
select s.sid from suppliers s inner join catalog c on c.sid = s.sid inner join parts p on p.pid = c.pid where p.color in ('red', 'green') -- either one or the other group by s.id having min(p.color) <> max(p.color) -- both are present