15

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;
  • 3
    What do you mean by "first" email? What sort criteria determines "first"? – Queue Mann Jun 11 '15 at 16:33
  • 1
    did you try top 1? – Racer SQL Jun 11 '15 at 16:33
  • 3
    Which DBMS are you using? Postgres? Oracle? – a_horse_with_no_name Jun 11 '15 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ᵀᴹ Jun 11 '15 at 18:29
  • 5
    Well, that's one valid criterion: "I don't care which row, just one". – ypercubeᵀᴹ Jun 11 '15 at 19:33
18
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 | |
  • 1
    What is the 'A'? Is it an abbreviation of the table name? – normandantzig Jun 11 '15 at 19:18
  • 2
    @normandantzig That's an alias. – Bacon Bits Jun 11 '15 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 Jun 11 '15 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ᵀᴹ Jun 11 '15 at 19:36
13

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

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 | |
4
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 | |
  • 1
    What is the 'P'. Is it an abbreviation of the table name? – normandantzig Jun 11 '15 at 19:18
  • 1
    'P' is an alias for Person. It follows the table declaration in the FROM clause. – Queue Mann Jun 11 '15 at 19:19
  • 1
    @normandantzig Both are valid in SQL Server in most situations. – Bacon Bits Jun 11 '15 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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