SQL Join query, getting manager name

Question

I have tblEmployeeProfile and tblPersonnel. tblPersonnel - HR table, consisting of all company employees; tblEmployeeProfile contains employee position information.

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 cannot get the correct request to get ManagerFullName

Answer 1

Not sure if this is the same for Tsql, but you will need two connection statements. 1st connection is a table of employee profiles for your staff. Second, attach the staff table to the profile table to grab the manager name. It 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 made a left outer join on the manager file was the odd event that the designated manager might not have.

Answer 2

You can 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 3

 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 4

Try the following:

 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 5

 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
• each person has a manager (this means that the CEO will be absent when you, as for him).

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