Hi,

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.