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]
-~----------~----~----~----~------~----~------~--~---
