calvin:
> 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
>
COUNT(DISTINCT city) != COUNT(city)
> 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]
-~----------~----~----~----~------~----~------~--~---
