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