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