> I really think using AND or OR good idea. Sorry, I meant to say:
I really think using AND or OR is not a good idea. calvin wrote: > 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] -~----------~----~----~----~------~----~------~--~---
