Either HAVING, or an additional GROUP BY field of person_postal_address.person_id should do, I think.
On Sat, Mar 14, 2009 at 8:38 PM, Nigel Peck <nigel.p...@miswebdesign.com> wrote: > Arthur Fuller wrote: >> >> Won't a simple LIMIT 1 do what you want? Or am I missing something? > > Thanks for getting back to me. You're missing something, but I probably > didn't explain very well. > > There can be multiple matches from the People table, but when more than one > address matches, I get more than one result per record in the People table > (one per matching address). > > Hopefully this will explain better: > > - "People" Table - > > person_id | name | > -=-=-=-=-=-=-=-=-=-=-= > 1 | Nigel | > 2 | Lindsey | > -=-=-=-=-=-=-=-=-=-=-= > > - "Person_postal_addresses" Table - > > person_id | address | > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > 1 | example road... | > 1 | example street... | > 2 | example lane... | > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= > > (person_id is FK of person_id in People table) > > If I'm searching for "example", I would currently get a result set of: > > 1 Nigel > 1 Nigel > 2 Lindsey > > (One result per matching address) > > I want the result set to be: > > 1 Nigel > 2 Lindsey > > (One result per "People" row that has a matching address) > > I could go through my result set and turn it into this, but I would rather > do it in the query as I'm sure that that will be more efficient. > > Cheers, > Nigel > >> On Fri, Mar 13, 2009 at 3:24 PM, Nigel Peck >> <nigel.p...@miswebdesign.com>wrote: >> >>> Nigel Peck wrote: >>> >>>> SELECT >>>> `People`.`person_id`, >>>> `People`.`name` >>>> FROM >>>> `People` >>>> INNER JOIN >>>> `Person_postal_addresses` >>>> ON >>>> `Person_postal_addresses`.`person_id` = `People`.`person_id` >>>> WHERE >>>> `People`.`name` REGEXP 'example' >>>> OR >>>> `Person_postal_addresses`.`address` REGEXP 'example' >>>> ; >>>> >>>> >>>> >>>> >> > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be > > -- Celsius is based on water temperature. Fahrenheit is based on alcohol temperature. Ergo, Fahrenheit is better than Celsius. QED. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
