Dmitry Ruban wrote:
>> +----+------------+------------+
>> | id | belongs_to | city |
>> +----+------------+------------+
>> | 1 | 1 | Auckland |
>> | 2 | 1 | Wellington |
>> | 3 | 2 | Auckland |
>> +----+------------+------------+
>>
>> Then how can I find a person who lives (or has houses) in Auckland and
>> Wellington?
>
> SELECT belongs_to FROM table
> WHERE city IN ('Auckland', 'Wellington')
> GROUP BY belongs_to
> HAVING COUNT(DISTINCT city) = 2
That would count people who have 2 addresses in either Auckland or
Wellington.
Why all the weird and wonderful solutions? The problem is trivial.
select name from person, address a1, address a2 where person.id =
a1.belongs_to and person.id = a2.belongs_to and a1.city = 'Auckland' and
a2.city = 'Wellington';
Rex
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
-~----------~----~----~----~------~----~------~--~---
