Oh yes, sorry, needs to be an INNER JOIN, not a LEFT JOIN. What I was referring to about creating a temp table (as part of the SELECT, and it's the temp table that you join) is so that you can create a single row per 'belongs_to' by joining all the address fields together, resulting in something like:
belongs_to address 1 Somewhere Road, Auckland Somewhere Street, Wellington ....and 'belongs_to' in the temp table would be unique. As I say, I haven't used MySQL for ages, so I'm not sure if that's doable, but I know it can be done in PostgreSQL. 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 1:54 p.m. To: [email protected] Subject: [phpug] Re: Database - select question. 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] -~----------~----~----~----~------~----~------~--~---
