sql query question / count

I have houses that belongs to streets. A user can buy several houses. How do I find out, if the user owns an entire street?

street table with columns (id/name)
house table with columns (id/street_id [foreign key]
owner table with columns (id/house_id/user_id) [join 开发者_如何学编程table with foreign keys]

So far, I'm using count which returns the result:

select count(*), street_id from owner left join house on owner.house_id = house.id group by street_id where user_id = 1
count(*) | street_id
3        | 1
2        | 2

A more general count:

select count(*) from house group by street_id returns:
count(*) | street_id
3        | 1
3        | 2

How can I find out, that user 1 owns the entire street 1 but not street 2?

Thanks.

Do a reverse query, query for any owners on the street that are not the user you are looking for. If your result set is > 0 that means a user does not own the entire street.

 select count(*), street_id from owner left join house on owner.house_id = house.id group by street_id where user_id != 1

This query lists all the users that own an entire street, and the street that they own:

SELECT DISTINCT o.user_id, h.street_id, s.name FROM owner o
INNER JOIN house h ON h.id = o.house_id
INNER JOIN street s ON s.id = h.street_id
LEFT OUTER JOIN house h2 ON h2.street_id = h.street_id AND h2.id <> h.id
INNER JOIN owner o2 ON o2.house_id = h2.id AND o2.user_id <> o.user_id
WHERE o2.user_id IS NULL

In this case, "owns an entire street", means that no other owner owns a house on the same street. This also assumes that you have data for every house and owner on the street.

The way it works is by trying to join every house with a house on the same street owned by a different owner. Where the join fails, it means there are no other houses with a different owner on the same street.