calvin:
> Hi ctx2002,
>
> Say address column is now city column.
>
> +----+------------+------------+
> | 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
>
> Regards,
> Calvin
>
> ctx2002 wrote:
>> On Dec 15, 12:32 pm, Calvin <[email protected]> wrote:
>>
>>> Hi all ;)
>>>
>>> I have a question on select statement in MySQL (can be applied in
>>> other DBMS's as well).
>>>
>>> Say I have two tables in a database, Person and Address. A person can
>>> have one or more addresses (one to many relationship). Here are some
>>> snippets of two tables
>>>
>>> mysql> select * from Person;
>>> +----+-----------------+
>>> | id | name |
>>> +----+-----------------+
>>> | 1 | John Doe |
>>> | 2 | Jessica Roberts |
>>> +----+-----------------+
>>> 2 rows in set (0.00 sec)
>>>
>>> mysql> select * from Address;
>>> +----+------------+----------------------------------------------+
>>> | id | belongs_to | address |
>>> +----+------------+----------------------------------------------+
>>> | 1 | 1 | Somewhere Road, Auckland CBD, Auckland |
>>> | 2 | 1 | Somewhere Street, Wellington CBD, Wellington |
>>> | 3 | 2 | Somewhere Ave, Auckland CBD, Auckland |
>>> +----+------------+----------------------------------------------+
>>> 3 rows in set (0.00 sec)
>>>
>>> I want to find a person with both Wellington address AND Auckland
>>> address. So I came up with the following:
>>>
>>> mysql> select p.*, count(*) as c from Person p left join Address a on
>>> (p.id = a.belongs_to) WHERE a.address like '%, Wellington' OR
>>> a.address like '%, Auckland' group by a.belongs_to having c >= 2;
>>> +----+----------+---+
>>> | id | name | c |
>>> +----+----------+---+
>>> | 1 | John Doe | 2 |
>>> +----+----------+---+
>>> 1 row in set (0.00 sec)
>>>
>>> The problem of this
>>> 1. If I want to look up more more addresses, the query gets messy and
>>> long.
>>>
>> your data modeling is not that right,
>> there is 3 entities, person, address, and city.
>>
>> 1 > a person have one or many addresses.
>> 2 > a address belongs to one or many city.
>> 3> a city contains zero or many addresses.
>>
>> so you need 4 tables.
>> otherwise you will always write those messy queries that slow and
>> hard to understand.
>>
>>
>>
>>> 2. Problem with `having` clause and OR. If a person has two addresses
>>> in Auckland and none other, `count(*)` still will be two, resulting in
>>> inaccurate result
>>>
>>> Is there anyway to solve this problem - I'm new to DBMS ;)
>>>
>>> Thanks in advance.
>>>
>>> Calvin
>>>
>>>
>>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
NZ PHP Users Group: http://groups.google.com/group/nzphpug
To post, send email to [email protected]
To unsubscribe, send email to
[email protected]
-~----------~----~----~----~------~----~------~--~---
