SQL Join query, getting ManagerName

Question

I have an tblEmployeeProfile & a tblPersonnel. tblPersonnel is an HR table, that consists of all employees in the company; tblEmployeeProfile contains details about an employee's position.

tblPersonnel.PersonnelID
tblPersonnel.FirstName
tblPersonnel.MiddleName
tblPersonnel.LastName
tblPersonnel.PhoneNumber
tblPersonnel.Email

tblEmployeeProfile.EmployeeID
tblEmployeeProfile.ManagerID
tblEmployeeProfile.DepartmentID
tblEmployeeProfile.JobCategoryID
tblEmployeeProfile.SalaryID

I want to return a record with the following fields:

EmployeeID, FirstName, MiddleName, LastName, Email, ManagerFullName

where EmployeeID = @EmployeeID

*tblEmployeeProfile.ManagerID = tblPersonnel.PersonnelID*

I can't seem to get the query correct for getting the ManagerFullName

Answer 1

You may want to try the following:

SELECT 
    e1.EmployeeID,
    e1.FirstName,
    e1.MiddleName,
    e1.LastName,
    e1.Email,
    e2.FirstName + ' ' + e2.LastName AS ManagerFullName
FROM
    tblPersonnel e1
INNER JOIN
    tblEmployeeProfile ep ON (ep.EmployeeID = e1.PersonnelID)
INNER JOIN
    tblPersonnel e2 ON (e2.PersonnelID = ep.ManagerID)
WHERE 
    e1.EmployeeID = @EmployeeID

Answer 2

SELECT
employee.PersonnelID, employee.FirstName, employee.MiddleName, employee.LastName, employee.Email, manager.FirstName + ' ' + manager.Surname
FROM
tblPersonnel AS employee
INNER JOIN tblEmployeeProfile ON employee.PersonnelID = tblEmployeeProfile.EmployeeID
INNER JOIN tblPersonnel AS manager ON tblEmployeeProfile.ManagerID = manager.PersonnelID
WHERE
employee.PersonnelID = @EmployeeID

Answer 3

Try this:

SELECT
   p1.PersonnelID,
   p1.FirstName,
   p1.MiddleName,
   p1.LastName,
   p1.Email,
   p2.FirstName + ' ' + p2.MiddleName + ' ' + p2.LastName as ManagerFullName
FROM
   tblEmployeeProfile e,
   tblPersonnel p1,
   tblPersonnel p2
WHERE
   e.EmployeeId = p1.PersonnelId AND
   e.ManagerId = p2.PersonnelId AND
   e.EmployeeId = @EmployeeId

Answer 4

select p.EmployeeID
     , E.FirstName
     , E.MiddleName
     , E.LastName
     , E.Email
     ,   M.FirstName 
       + ' ' 
       + M.MiddleName
       + ' ' 
       + M.LastNameas as ManagerFullName
from tblPersonnel p
join tblEmployeeProfile as E on p.PersonnelID=E.EmplyeeID
join tblEmployeeProfile as M on M.PersonnelID=E.ManagerID
where E.EmplyeeID=@EmployeeID

assuming of course:

• tblPersonnel.PersonnelID = tblEmployeeProfile.EmployeeID
• every person has a manager (this means the CEO will be missing when you as for him/her)

Answer 5

Not sure if it's the same for Tsql, but you're going to need two join statements. 1st join is the employee profile table to your personnel table. Second is to join the personnel table to to the profile table to grab the manager name. Might look something like this

FROM personnel p
JOIN employeeprofile prof
ON prof.employeeID = p.personnelID
LEFT OUTER JOIN personnel man
ON man.personnelID = prof.managerID

The only reason I did a left outer join on the manager stuff is in the odd event a user might not have a manager assigned.

Answer 6

Are you trying to do something like this?

Select
    EmployeeID
    ,empTable.FirstName
    ,empTable.MiddleName
    ,empTable.LastName
    ,empTable.Email
    ,mgrTable.FirstName + ' ' 
   + mgrTable.MiddleName + ' '
   + mgrTable.LastName
      as ManagerFullName
from
    tblEmployeeProfile
    inner join
    tblPersonnel as empTable
        on tblEmployeeProfile.EmployeeID = empTable.PersonnelID
    inner join
    tblPersonnel as mgrTable
        on tblEmployeeProfile.ManagerID = mgrTable.PersonnelID
where EmployeeID = @EmployeeID