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