17

This is related to this question: Joining multiple tables results in duplicate rows

I have two tables that I am joining. They share a key. The person table has one name per primary key but the email table has multiple emails per personId. I want to only show the first email per person. Presently I get multiple rows per person because they have multiple emails. I am running SQL-Server 2005.

EDIT: This is T-SQL. First email is literally the first email row per person.

Edit 2: First email as I see it would be the first email row that shows up in the join as SQL works through the query. I does not matter which email shows up. Only that no more than one email shows up. I hope that makes it clearer. 

Table1: Person
Table2: Email

Select Person.PersonName, Email.Email
From person 
left join on Person.ID=Email.PersonId;
Improve this question
6
  • 3
    What do you mean by "first" email? What sort criteria determines "first"?
    – Queue Mann
    Commented Jun 11, 2015 at 16:33
  • 1
    did you try top 1?
    – Racer SQL
    Commented Jun 11, 2015 at 16:33
  • 3
    Which DBMS are you using? Postgres? Oracle?
    – user1822
    Commented Jun 11, 2015 at 17:15
  • 3
    The "the first email row per person" does not really tell us anything. "First" by what criteria? SQL tables have no inherent order.
    – ypercubeᵀᴹ
    Commented Jun 11, 2015 at 18:29
  • 7
    Well, that's one valid criterion: "I don't care which row, just one".
    – ypercubeᵀᴹ
    Commented Jun 11, 2015 at 19:33

4 Answers 4

Reset to default
25 
SELECT
    A.PersonName, A.Email
FROM
        (
        Select Person.PersonName, Email.Email
            ,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN
        From person 
        left join Email on Person.ID=Email.PersonId
        ) A
WHERE A.RN = 1
Improve this answer
6
  • 1
    What is the 'A'? Is it an abbreviation of the table name?
    – normandantzig
    Commented Jun 11, 2015 at 19:18
  • 2
    @normandantzig That's an alias.
    – Bacon Bits
    Commented Jun 11, 2015 at 19:28
  • 1
    1. ) Did you alias both of my tables using the From (Select Person.PersonName, Email.Email ,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN From person left join on Person.ID=Email.PersonId) A And 2. ) so that you could refer to both columns using A as the table?
    – normandantzig
    Commented Jun 11, 2015 at 19:32
  • 4
    What is inside the parenthesis is called a derived table. It's a valid table, just as base tables but it's lifetime is only for the duration of the query execution. It must have a name (or alias) and @sabin chose to name it A. This table has 3 columns (PersonName, Email, RN) and outside of the parentheses, you can use A.Email just as you can use tablename.columnname for every other table in your code.
    – ypercubeᵀᴹ
    Commented Jun 11, 2015 at 19:36
  • Care to explain your answer?
    – Cardinal System
    Commented Sep 29, 2023 at 13:03
15

I would use an outer apply for this, I find it more readable.

Select Person.PersonName, coalesce(Email.Email,'No email found.') as Email
From person 
outer apply (
  select top(1) Email.Email 
  from Email 
  where Person.ID=Email.PersonId
  order by <whatever suits you>
) as Email;
Improve this answer
5 
select
  P.PersonID,
  (SELECT TOP 1 E.Email FROM Email E WHERE E.PersonID = P.PersonID ORDER BY <pick your column here>)
from
  Person P
Improve this answer
3
  • 1
    What is the 'P'. Is it an abbreviation of the table name?
    – normandantzig
    Commented Jun 11, 2015 at 19:18
  • 1
    'P' is an alias for Person. It follows the table declaration in the FROM clause.
    – Queue Mann
    Commented Jun 11, 2015 at 19:19
  • 1
    @normandantzig Both are valid in SQL Server in most situations.
    – Bacon Bits
    Commented Jun 11, 2015 at 19:30
5

As it does not matter which email shows up. I think that the following one is very direct.

Select Person.PersonName,  MIN(Email.Email)
From person 
left join email 
on Person.ID=Email.PersonId
group by Person.Id, Person.PersonName
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.