Not a definite answer, but food for thought: I haven't used MySQL for ages, but is there a function which will allow you to join the multiple addresses together into a single string? If so, then you could join that temp table to Person via:
LEFT JOIN tempAddress a ON (p.id = a.belongs_to AND a.address like '%, Wellington%' AND a.address like '%, Auckland%') I think that makes sense? Thanks, Nathan. Web Controlled Robotic Arm, Beetle, Missile Launcher and Xmas Tree/Lights! http://www.kennedytechnology.com -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Calvin Sent: Monday, 15 December 2008 12:33 p.m. To: NZ PHP Users Group Subject: [phpug] Database - select question. 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. 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] -~----------~----~----~----~------~----~------~--~---
