Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is a better way to write this SQL query?

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"
        )
);
like image 556
Guan Summy Huang Avatar asked Oct 01 '15 05:10

Guan Summy Huang


People also ask

Where do you write SQL queries?

Microsoft SQL Server Management Studio (SSMS) Microsoft SQL Server Management Studio allows users to create and edit SQL queries and manage databases.


2 Answers

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"
like image 124
pmains Avatar answered Nov 09 '22 16:11

pmains


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

like image 28
dcieslak Avatar answered Nov 09 '22 17:11

dcieslak