Search code examples
mysqlsqljoingroup-byhaving-clause

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

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!

Solution

  • 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