Rex has his point point
> That would count people who have 2 addresses in either Auckland or
> Wellington.
This is a problem of using `OR` and `having count` at the same time.
Calvin
Dmitry Ruban wrote:
> Rex Johnston:
>
>> 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';
>>
>>
>
> Well, the query i posted above has following advantage: it can be easily
> generated in php, for instance
>
> $cities = array('Auckland', 'Wellington');
> // or $cities = array('Auckland', 'Rotorua', 'Gisborne');
> // or $cities = array('Wellington');
>
> $query = "
> SELECT belongs_to FROM table
> WHERE city IN ('" .
> implode("','", array_map('mysql_real_escape_string', $cities)) .
> "') GROUP BY belongs_to
> HAVING COUNT(DISTINCT city) = " . count($cities);
>
> and now try to generate query based on inner join approach.
>
>
>
>> 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]
-~----------~----~----~----~------~----~------~--~---
