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=arch...@jab.org
