Shpanders Shpanders - 1 month ago 6
MySQL Question

Check if row exists in other table

Given database schema:

Part( P#, PName, Producer, Year, Price)
Customer( C#, CName, Province)
Supply(S#, P#, C#, Quantity, Amount, Date)


How would I create a query to list names and provinces of the customers who did not buy any Apple products?

I have:

SELECT
b.cname,
b.province
FROM
part c,
customer b,
supply a
WHERE
c.PID = a.PID
AND b.CID = a.CID
AND c.producer != 'Apple'


However this returns all customers who bought something other than an Apple product. So I need to be able to compare to other records.

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