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? > > 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. >>
You aint necessarily may need to do further normalization in this case, otherwise you end up having tables - country, city, street etc. which may not be required for this task. i'd suggest to add new column "city" into this table and keep address in free format if you are not going extract specific info from it. >> >> >>> 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] -~----------~----~----~----~------~----~------~--~---
