List:General Discussion« Previous MessageNext Message »
From:Nigel Peck Date:March 14 2009 7:38pm
Subject:Re: Getting single results per (left) record with INNER JOIN
View as plain text  
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'
>>> ;
>>>
>>>
>>>
>>>
> 
Thread
The <=> operatorMorten13 Mar
  • Re: The <=> operatorMichael13 Mar
    • avoiding use of Nulls (was: The <=> operator)Ray13 Mar
      • Re: avoiding use of Nulls (was: The <=> operator)michael13 Mar
        • Re: avoiding use of Nulls (was: The <=> operator)Thomas Spahni13 Mar
          • Re: avoiding use of Nulls (was: The <=> operator)michael13 Mar
            • Re: avoiding use of NullsAndy Wallace13 Mar
              • Re: avoiding use of NullsArthur Fuller14 Mar
            • Re: avoiding use of NullsPJ13 Mar
            • Re: avoiding use of Nulls (was: The <=> operator)Claudio Nanni14 Mar
              • Re: avoiding use of Nulls (was: The <=> operator)Morten14 Mar
              • Re: avoiding use of Nulls (was: The <=> operator)michael14 Mar
                • Re: avoiding use of Nulls (was: The <=> operator)Mattia Merzi15 Mar
                • Re: avoiding use of Nulls (was: The <=> operator)Claudio Nanni15 Mar
                  • Re: avoiding use of Nulls (was: The <=> operator)Don Read15 Mar
                    • W2008 Server Issues?MCUSA)16 Mar
                      • Re: W2008 Server Issues?David M. Karr16 Mar
            • Re: avoiding use of Nulls (was: The <=> operator)Mattia Merzi14 Mar
        • Re: avoiding use of Nulls (was: The <=> operator)Ray14 Mar
          • Re: avoiding use of NullsAMichel Durand14 Mar
      • Getting single results per (left) record with INNER JOINNigel Peck13 Mar
        • Re: Getting single results per (left) record with INNER JOINNigel Peck13 Mar
          • Re: Getting single results per (left) record with INNER JOINArthur Fuller14 Mar
            • Re: Getting single results per (left) record with INNER JOINNigel Peck14 Mar
              • Re: Getting single results per (left) record with INNER JOINJohan De Meersman15 Mar
                • Re: Getting single results per (left) record with INNER JOINNigel Peck15 Mar
        • Re: Getting single results per (left) record with INNER JOINJoerg Bruehe16 Mar