lundi 7 décembre 2015

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

Aucun commentaire:

Enregistrer un commentaire