CODEWITHSUNDEEP
mysqlsqljoinleft-join
MegaHit
MegaHit

Reputation: 2644

left join, with only 1 row per left entry

Here's my table structure:

persons
-------
id
first_name
last_name


phone_numbers
-------------
id
person_id
number
type

Here's my query:

SELECT `persons`.`id`, `first_name`, `last_name`, `number`
FROM `persons`
LEFT JOIN `phone_numbers` ON `persons`.`id` = `phone_numbers`.`person_id`
ORDER BY `last_name` ASC

This returns all people regardless of whether they have a phone number. Good.

The problem starts when a person has more than 1 phone number; I get multiple rows for the same person.

How can I ensure that I only ever get 1 row per person (with the first phone number, or non-at-all - if there aren't any)?

UPDATE: I forgot to include the fact that I have a type column. It's value can be one of three choices:

  1. Cell
  2. Work
  3. Home

First would be determined by that order.

Upvotes: 1

Views: 319

Answers (1)

eggyal
eggyal

Reputation: 125855

SELECT * FROM phone_numbers NATURAL JOIN (
  SELECT   person_id,
           ELT(
             MIN(FIELD(type, 'Cell', 'Work', 'Home')),
             'Cell', 'Work', 'Home'
           ) AS type
  FROM     phone_numbers
  GROUP BY person_id
) t JOIN persons ON persons.id = phone_numbers.person_id

Upvotes: 3

Related Questions