How to select first row from a join that returns multiple rows on the primary key

Question

I have two tables that I am joining. They share a key. The employee table has one name per primary key but the email table has multiple emails per employeeId.

I want to only show the first email per employee. Presently I get multiple rows per employee because they have multiple emails. I am running SQL Server 2019.

Edit: 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 do not matter which email shows up. Only that no more than one email shows up. I hope that makes it clearer.

My current query:

Select Employee.PersonName, Email.Email
From Employee
left join on Employee.ID = Email.employeeId;

Answer 1

One method uses a subquery:

select ep.personname, em.*
from employee ep
cross apply (
    select top (1) em.email 
    from email em   
    where em.employeeid = ep.id 
    order by em.email
) em

If you just want the email and nothing else, aggregation is also OK:

select ep.personname, 
    (select min(em.email) from email em where em.employeeid = ep.id) as email
from employee ep

Or:

select ep.personname, min(em.email) as email
from employee ep
left join email em on em.employeeid = ep.id
group by ep.id, ep.personname