Self Join to get the name of manager manager

Question

Hello, I have an Employee table with the following columns

Emp_id, Emp_Name and Mgr_id. 

I'm trying to create a view that will display

Emp_id, Emp_name, Mgr_id and Mgr_name (by crossing the Employee table). I tried outer join, inner join, etc., but I can't fix it.

Any help is appreciated.

 CREATE TABLE [dbo].[tblEmployeeDetails]( [emp_id] [bigint] NOT NULL, [emp_name] [nvarchar](200) NULL, [emp_mgr_id] [bigint] NULL, CONSTRAINT [PK_tblEmployeeDetails] PRIMARY KEY CLUSTERED ( [emp_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] 

Answer 1

 CREATE VIEW AS SELECT e1.emp_Id EmployeeId, e1.emp_name EmployeeName, e1.emp_mgr_id ManagerId, e2.emp_name AS ManagerName FROM tblEmployeeDetails e1 JOIN tblEmployeeDetails e2 ON e1.emp_mgr_id = e2.emp_id 

EDIT : left join will work if emp_mgr_id is null.

 CREATE VIEW AS SELECT e1.emp_Id EmployeeId, e1.emp_name EmployeeName, e1.emp_mgr_id ManagerId, e2.emp_name AS ManagerName FROM tblEmployeeDetails e1 LEFT JOIN tblEmployeeDetails e2 ON e1.emp_mgr_id = e2.emp_id 

Answer 2

  SELECT b.Emp_id, b.Emp_name,e.emp_id as managerID, e.emp_name as managerName FROM Employee b JOIN Employee e ON b.Emp_ID = e.emp_mgr_id 

Try this, this is JOIN on yourself to get a manager :)

Answer 3

 CREATE VIEW EmployeeWithManager AS SELECT e.[emp id], e.[emp name], m.[emp id], m.[emp name] FROM Employee e LEFT JOIN Employee m ON e.[emp mgr id] = m.[emp id] 

This definition uses a left outer join, which means that even employees with a manager identifier of NULL will be listed or whose manager has been deleted (if your application allows this), and their manager attributes will be returned as NULL.

If you used an internal join instead, only those with managers will be listed.

Answer 4

As Jesse said, use self join:

 SELECT e.emp_id , e.emp_name , e.emp_mgr_id , m.emp_name AS mgr_name FROM [dbo].[tblEmployeeDetails] e LEFT JOIN [dbo].[tblEmployeeDetails] m ON e.emp_mgr_id = m.emp_id 

Answer 5

  select E1.emp_id [Emp_id],E1.emp_name [Emp_name], E2.emp_mgr_id [Mgr_id],E2.emp_name [Mgr_name] from [tblEmployeeDetails] E1 left outer join [tblEmployeeDetails] E2 on E1.emp_mgr_id=E2.emp_id 

Answer 6

Try it.

 SELECT Employee.emp_id, Employee.emp_name,Manager.emp_id as Mgr_Id, Manager.emp_name as Mgr_Name FROM tblEmployeeDetails Employee LEFT JOIN tblEmployeeDetails Manager ON Employee.emp_mgr_id = Manager.emp_id 

Answer 7

 SELECT e1.empno EmployeeId, e1.ename EmployeeName, e1.mgr ManagerId, e2.ename AS ManagerName FROM emp e1, emp e2 where e1.mgr = e2.empno 

Answer 8

try it. you have to do LEFT JOIN to fill in the null values ​​in the table

 SELECT a.emp_Id EmployeeId, a.emp_name EmployeeName, a.emp_mgr_id ManagerId, b.emp_name AS ManagerName FROM tblEmployeeDetails a LEFT JOIN tblEmployeeDetails b ON b.emp_mgr_id = b.emp_id 

Answer 9

 TableName :Manager EmpId EmpName ManagerId 1 Monib 4 2 zahir 1 3 Sarfudding NULL 4 Aslam 3 select e.EmpId as EmployeeId,e.EmpName as EmployeeName,e.ManagerId as ManagerId,e1.EmpName as Managername from Manager e join manager e1 on e.ManagerId=e1.empId 

Answer 10

 create table abc(emp_ID int, manager varchar(20) , manager_id int) emp_ID manager manager_id 1 abc NULL 2 def 1 3 ghi 2 4 klm 3 5 def1 1 6 ghi1 2 7 klm1 3 select a.emp_ID , a.manager emp_name,b.manager manager_name from abc a left join abc b on a.manager_id = b.emp_ID Result: emp_ID emp_name manager_name 1 abc NULL 2 def abc 3 ghi def 4 klm ghi 5 def1 abc 6 ghi1 def 7 klm1 ghi 

Answer 11

 create view as (select e1.empno as PersonID, e1.ename as PersonName, e2.empno MANAGER_ID, e2.ename MANAGER_NAME from employees e1 , employees e2 where e2.empno=e1.mgr) 

Answer 12

 select E1.EmpId,E1.Name,E2.Name as Manager from Employee E1 left join Employee E2 on E1.ManagerID = E2.EmpId 

Answer 13

In addition, you may want to attract managers and their reports using

 SELECT e2.ename ,count(e1.ename) FROM employee_s e1 LEFT OUTER JOIN employee_s e2 ON e1.manager_id = e2.eid group by e2.ename; 

Answer 14

 SELECT e1.emp_id, e1.emp_name, e1.mgr_id, e2.emp_name as manager_name FROM employee e1 JOIN employee e2 ON e1.mgr_id = e2.emp_id ORDER BY e1.emp_id 

* Here is a link to SQL Fiddle with a working example. http://www.sqlfiddle.com/#!17/392b5/9