CODEWITHSUNDEEP
sqlsql-serversql-server-2012sql-server-2014
SkyeBoniwell
SkyeBoniwell

Reputation: 7112

SELECT and JOIN to return only one row for each employee

I have a user table that stores the employeeId, lastName, firstName, department, hire date and mostRecentLogin.

I have another table that stores the employeeId, emailAddress.

The emailAddress table can have multiple rows for an employee if they have multiple email addresses.

I'm trying to return results that only show one row for each employee. I don't care which email address, just as long as it only picks one.

But all the queries I've tried always return all possible rows.

Here is my most recent attempt:

select *
from EmployeeInfo i
left join EmployeeEmail e ON i.employeeId = e.employeeId
where i.hireDate = 2015 
and employeeId IN (
    SELECT MIN(employeeId)
    FROM EmployeeInfo
    GROUP BY employeeId
)

But then again, this returns all possible rows.

Is there a way to get this to work?

Upvotes: 1

Views: 1154

Answers (2)

Dale K
Dale K

Reputation: 27290

Use a sub-query instead of a join:

select *
  , (select top 1 E.EmailAddress from EmplyeeEmail E where E.employeeId = I.employeeId)
from EmployeeInfo I
where I.hireDate = 2015;

Note: If you change your mind and decide you do have a preference as to which email address is returned then just add an order by to the sub-query - otherwise it is truly unknown which one you will get.

Upvotes: 2

ubuntuuber
ubuntuuber

Reputation: 732

This should work. 

SELECT *
FROM EmployeeInfo
Left JOIN EmployeeEmail
  ON EmployeeInfo.employeeId = EmployeeEmail.employeeId
WHERE EmployeeInfo.hireDate = '2015'
GROUP BY EmployeeInfo.employeeId;

Upvotes: 0

Related Questions