sql query question / count
- by scheibenkleister
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.
