CODEWITHSUNDEEP
sql-server-2005
ImDeveloping
ImDeveloping

Reputation: 101

How to SELECT unique rows from 3 JOINED tables

I am trying to check if a persons name appears multiple times in a table aswell as pull additional information (position title, department name, employee number) to limit creating two stored procedures 1. to check if name appears multiple times and 2. to get the additional information after check has been completed.

my current query right now is as follows, which returns multiple rows as the ID I am using to join the tables appears multiple times in one table (an employee can belong to multiple departments, the EmpID occurs here multiple times thyus returning the multiple values).

SELECT c.FirstName+ ' ' + c.LastName as emp_full_name, e.EmployeeNumber,
       e.EmpID, dh.PositionTitle, d.Name as deptName, e.isActive

FROM Person.Contact c

INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeeDepartmentHistory dh ON e.EmpID = dh.EmpID
INNER JOIN HumanResources.Department d ON dh.DepartmentID = d.DepartmentID

WHERE c.FirstName+ ' ' + c.LastName LIKE @empName

My general table structure is as follows:

Person.Contact

ContactID

FirstName

LastName

HumanResources.Employee

EmpID

EmployeeNumber

isActive

HumanResources.EmployeeDepartmentHistory

DepartmentHistoryId

EmpID

DepartmentID

PositionTitle

HumanResources.Department

DepartmentID

Name

Example Result Set:

John Doe 1234567  1 Database Architect Administration    A
John Doe 1234567  1 Database Tester    Administration    A

Upvotes: 0

Views: 33

Answers (1)

Syeda
Syeda

Reputation: 1205

Use Distinct keyword

SELECT distinct c.FirstName+ ' ' + c.LastName as emp_full_name, e.EmployeeNumber,
       e.EmpID, STUFF((
      SELECT ',' + edh.PositionTitle
      FROM dbo.EmployeeDepartmentHistory edh
      WHERE edh.EmpID = e.EmpID
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''), e.isActive

FROM Person.Contact c

INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeeDepartmentHistory dh ON e.EmpID = dh.EmpID
INNER JOIN HumanResources.Department d ON dh.DepartmentID = d.DepartmentID

WHERE c.FirstName+ ' ' + c.LastName LIKE @empName
Group By e.EmpID

Upvotes: 1

Related Questions