From: Nigel Peck (nigel.peckmiswebdesign.com)
Date: Sat Mar 14 2009 - 14:38:09 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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.peckmiswebdesign.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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]