Thanks for the try, but I was looking for the different suppliers names not the

Question

Thanks for the try, but I was looking for the different suppliers names not the name of the part.  In fact with a clue from another student I got the correct answer:

SELECT DISTINCT S.SNAME FROM S WHERE S.SNO IN  (SELECT SP2.SNO  FROM SP AS SP2  WHERE EXISTS  (SELECT *  FROM SP  WHERE  SP.PNO = SP2.PNO  AND SP.SNO='S3'));

Sorry, This question has already been solved, and the one response given wasn't accurate.

Question? SQL - get supplier names for suppliers who supply a part supplied by S3 using EXISTS.
Tables supplied -
Suppliers(S) {SNO-key, Sname, Status, City},
record (S3, Blake, 30, Paris)

Parts(P) {PNO-key, PName, Color, Weight, StoredCity},

Shipment(SP) {SNO-key, PNO-key, QTY}.
record (S3, P2, 200)

This is the SQL statement I've come up with -

SELECT DISTINCT S.SNAME
FROM S INNER JOIN SP ON S.SNO = SP.SNO
WHERE EXISTS (
Select S.SNO
From S
Where SP.PNO = 'P2');

problem is the parameter 'P2' is supposed to be passed in after finding it using S3, and I don't know how to pass a value in this situation - where the target value is (an unknown) in another column (use SNO to find PNO's value then return PNO's value). Remember, this SQL statement must use Exists or Not Exists.
Thank you.

Explanation / Answer

Dear, Select distinct pname from parts where exists (select * from shipments where parts.p#=shipments.p# and shipments.s#='s3'); Hope this will help you.

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.