Saturday, 31 August 2013

Relational division - SQL

Relational division - SQL

I have 3 tables.
Owner(owner_id, name)
House(code, owner_id, price)
Buyer(buyer_id, name)
Bought(buyer_id, code, price_bought, date_bought)
I have the following query:
List the names of the buyers that bought all the houses from some owner?
I know how to find if someone bought all the houses from a particular
owner (say owner with id = 1):
SELECT name
FROM buyer
WHERE NOT EXISTS (SELECT code
FROM house
WHERE owner_id = 1
AND code NOT IN (SELECT code
FROM bought
WHERE bought.buyer_id=
buyer.buyer_id))
How can I make this work for all owners?

No comments:

Post a Comment