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

Question

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?

your query looks fine to me. why you want to rewrite it? – Rahul Oct 19 '16 at 18:48 — Rahul, Oct 19 '16 at 18:48

score 0 · Answer 1 · answered Oct 19 '16 at 18:51

You want customer who only bought Apple products?

One possible solution is based on conditional aggregation:

Select c.cname, c.Province
From Customer c
join
 ( -- this is not a Subquery, it's a Derived Table
   Select s.CId -- assuming there's a CId in Supply
   from Supply s
   join Part p
     on p.pId = s.pId
   group by s.CId 
   -- when there's any other supplier this will return 1
   having max(case when p.Producer = 'Apple' then 0 else 1 end) = 0
 ) as p
on p.CId = c.CId

score 0 · Accepted Answer · edited May 23 '17 at 10:32

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.

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

2 Answers2