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