SQL How to ask for a value multiply times in an Query [closed]

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)
)

​x
 
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!

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

 
select s.sidfrom suppliers sinner join catalog c on c.sid = s.sidinner join parts p on p.pid = c.pidwhere p.color in ('red', 'green')    -- either one or the othergroup by s.idhaving min(p.color) <> max(p.color)  -- both are present​

Advertisement

Answer