asked on

sql query

I have three table
supplier(sid,sname)
part(pid,pname,color)
catalogue(sid,pid,cost)

I want to find the sname of suppliers who supply EVERY RED part
My query is as the follow :it doesnt return any row for me.What is wrong???

SELECT s.sname, p.pid
FROM supplier AS s, part AS p, catalogue AS c
WHERE (((p.pid)=c.pid) And ((s.sid)=c.sid))
and
PID IN (SELECT PID FROM PARTS WHERE COLOR=""RED")

I think this only returns suppliers naem who supply any red parts.

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nigelrivett

select s.sid, s.sname
FROM supplier s
join catalogue c
on s.sid = c.sid
join part p
on p.pid = c.pid
where p.color = 'Red'
group by s.sid, s.sname
having count(distinct p.pid) = (select count(distinct pid) from parts where color = 'Red')

Dishan Fernando

supplier(sid,sname)
part(pid,pname,color)
catalogue(sid,pid,cost)

SELECT S.sname
FROM supplier S INNER JOIN catalogue C
ON S.sid = C.sid INNER JOIN Part P
ON C.pid = P.pid AND P.color = 'RED'

Lowfatspread

ok have i misread this?

1) do you want just ther suppliers
who supply the full range of parts for which the colour is red?

2) or the names of suppliers who supply RED parts ?

my query and nigel's attempt to do 1
(nigels would work only if catalogue has a priamary key of sid,pid )
dishanf does 2