Hi Nathan, I ran some queries as you suggested but returned similar results as previous.
the AND mysql> select * from Person p left join Address a on (p.id = a.belongs_to and (a.address like '%, Auckland' OR a.address like '%, Wellington')); +----+-----------------+------+------------+----------------------------------------------+ | id | name | id | belongs_to | address | +----+-----------------+------+------------+----------------------------------------------+ | 1 | John Doe | 1 | 1 | Somewhere Road, Auckland CBD, Auckland | | 1 | John Doe | 2 | 1 | Somewhere Street, Wellington CBD, Wellington | | 2 | Jessica Roberts | 3 | 2 | Somewhere Ave, Auckland CBD, Auckland | +----+-----------------+------+------------+----------------------------------------------+ 3 rows in set (0.00 sec) the OR mysql> select * from Person p left join Address a on (p.id = a.belongs_to and a.address like '%, Auckland' and a.address like '%, Wellington'); +----+-----------------+------+------------+---------+ | id | name | id | belongs_to | address | +----+-----------------+------+------------+---------+ | 1 | John Doe | NULL | NULL | NULL | | 2 | Jessica Roberts | NULL | NULL | NULL | +----+-----------------+------+------------+---------+ 2 rows in set (0.00 sec) I really think using AND or OR good idea. AND and OR are for comparing columns in a row, aren't they? In here, it's comparing a single column across multiple rows. Best, Calvin Nathan Kennedy wrote: > 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] -~----------~----~----~----~------~----~------~--~---
