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.peck@stripped>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'
>>> ;
>>>
>>>
>>>
>>>
>
