Shpanders Shpanders - 1 month ago 4
MySQL Question

Rewriting a query that has two sub queries using no sub queries

Given the database schema:

Part( PID, PName, Producer, Year, Price)
Customer( CID, CName, Province)
Supply(SID, PID, CID, Quantity, Amount, Date)


And the query:

Select cname, Province
From Customer c
Where exists (
Select *
from Supply s
join Part p on p.pId = s.pId
Where CId = c.CId
and p.Producer = 'Apple'
)
and Not exists (
Select *
from Supply n
join Part nap on nap.pId = n.pId
Where CId = c.CId
and nap.Producer != 'Apple'
)


How would I go about rewriting this query without the two sub queries?

Answer

You can use the LEFT JOIN/NULL pattern to find customers who haven't bought any non-Apple products. Then you can do this all with just joins. You'll have to join with Supply and Parts twice, once for finding Apple products, then again for excluding non-Apple products.

SELECT distinct c.name, c.province
FROM Customer AS c
JOIN Supply AS s1 ON s1.cid = c.cid
JOIN Parts AS p1 ON p1.pid = s1.pid
LEFT JOIN Supply AS s2 ON s2.cid = c.cid
LEFT JOIN Parts AS p2 ON p2.pid = s2.pid AND p2.producer != 'Apple'
WHERE p1.producer = 'Apple' AND p2.pid IS NULL

Notice that in the LEFT JOIN you put restrictions of the second table in the ON clause, not the WHERE clause. See Return row only if value doesn't exist for more about this part of the query.