List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:March 14 2009 11:48pm
Subject:Re: Getting single results per (left) record with INNER JOIN
View as plain text  
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.peck@stripped> 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.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'
>>>> ;
>>>>
>>>>
>>>>
>>>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>



-- 
Celsius is based on water temperature.
Fahrenheit is based on alcohol temperature.
Ergo, Fahrenheit is better than Celsius. QED.
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