• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 917
  • Last Modified:

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.
 
0
ta2000
Asked:
ta2000
  • 2
1 Solution
 
LowfatspreadCommented:
Select * from Supplier as RS
Where rs.sid not in  
(Select s.Sid
    from Supplier as S,
   (select pid
      from part
     where color = 'RED') as P
   Where Not exists (select c.sid from catalogue C
                              where s.sid = c.sid and P.Pid = c.pid)
   )

0
 
nigelrivettCommented:
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')
0
 
Dishan FernandoSoftware Engineer / DBACommented:
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'
0
 
LowfatspreadCommented:
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

0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now