I have the following relations.
Suppliers( sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog( sid: integer, pid: integer, cost: real)
The problem asks me to find the pnames of parts supplied by Acme Widget Suppliers and no one else. I wrote the following SQL statement; however I feel like this query is inefficient because of the repetition. I would like to know if there is a better way to write this query without repeating the selecting catalog part.
Select P.pname
FROM Parts P
WHERE P.pid IN (
Select C.pid
FROM Catalog C
INNER JOIN Supplier S
ON S.sid = C.sid
WHERE S.sname = "Acme Widget Suppliers"
AND C.pid NOT IN (
SELECT C2.pid
FROM Catalog C2
INNER JOIN Supplier S
ON S.sid = C2.sid
WHERE S.sname <> "Acme Widget Suppliers"
)
);
Microsoft SQL Server Management Studio (SSMS) Microsoft SQL Server Management Studio allows users to create and edit SQL queries and manage databases.
You are correct. The query you want to make can be greatly simplified. Try the following.
SELECT P.pname FROM Parts P, Suppliers S, Catalog C
WHERE C.pid = P.pid
AND C.sid = S.sid
AND S.sname == "Acme Widget Suppliers"
The correct query would be:
Select P.PName from Suppliers S1
join Catalog C1
on S1.sid = C1.sid
join parts P
on P.pid = C1.pid
where S1.sname = 'Acme Widget Suppliers'
and not exists
( select 1 from catalog C2 where C2.sid != C1.sid
and C2.pid = C1.pid)
The not exist is very efficient as it stops to search when it finds the first occurence.
SQL Fiddle
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With