1

Here are the tables I am using:

  • parts(pid,pname,color)
  • suppliers(sid,sname,address)
  • catalog(sid,pid,cost)

I need to find the name of suppliers that supply two different parts with the same price. I keep getting the incorrect answer.

SELECT S.sname
FROM Supplers S
WHERE S.sid IN (SELECT C.pid
FROM Catalog C, Catalog C1, Parts P, Parts P1
WHERE C1.sid = C.sid  AND P.pid = P1.pid AND C1.cost = C.cost);
2

My best guess is that what you are actually trying to query is:

SELECT S.sname
FROM Suppliers S
WHERE S.sid IN (SELECT C.sid
FROM Catalog C, Catalog C1
WHERE C1.pid <> C.pid  AND C1.cost = C.cost);

I changed the subselect to return C.sid, and removed the parts table altogether, since in your original body text you stated no conditions on the table.

Is this what you are looking for?

  • Yes, Thanks I'm a beginner with a professor that is not much help – gigi Jun 18 '15 at 20:06
1

For Chris's answer, doesn't that give the answer across different suppliers? Shouldn't it be for the same supplier? So would the SQL be (using Chris's SQL):

SELECT S.sname
FROM Suppliers S
WHERE S.sid IN (SELECT C.sid
FROM Catalog C, Catalog C1
WHERE C1.pid <> C.pid  AND C1.cost = C.cost and c1.sid = c2.sid);
  • Are you saying or asking this? – dezso Jun 19 '15 at 10:31
  • The original question doesn't specify if it's for the same supplier or different suppliers. Either way it would be an assumption. – steve Jun 19 '15 at 11:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.