List:General Discussion« Previous MessageNext Message »
From:Nigel Peck Date:March 13 2009 7:17pm
Subject:Getting single results per (left) record with INNER JOIN
View as plain text  
I'm hoping someone can point me in the right direction for what I need, 
to save me trawling through books and Google when I don't know what I'm 
looking for.

I'm using an INNER JOIN to query a table that has a one-to-many 
relationship with the table in my FROM clause, but I only want one 
result in the result set per record in the table in my FROM clause, 
rather than multiple results if there are multiple matching records in 
the JOINed table.

I'm just not sure how to do that (or rather can't remember!).

My query is:

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'
;

How do I alter that to get one result per matching row in the People 
table? When there are multiple matches, for one row in People, for:

     `Person_postal_addresses`.`address` REGEXP '1'

Thanks in advance,
Nigel
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