Self Join to get employee manager name

Question

Hello I have a Employee Table with following columns  Emp id  Emp Name and Mgr id    I am trying to create a view which will list   Emp id  Emp name  Mgr id and Mgr name  by cross joining the Employee table   I tried outer join  inner join etc  but I am not able to get it right    Any help is highly 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

User · Answer

try this   you should do LEFT JOIN to igore 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

User · Answer

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 whose manager ID is NULL  or whose manager has been deleted  if your application allows that  will be listed  with their manager s attributes returned as NULL   If you used an inner join instead  only people who have managers would be listed

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  it s a JOIN on itself to get the manager

User · Answer

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

User · Answer

Try this one   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

User · Answer

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 the link to SQL Fiddle with a working example  http   www sqlfiddle com   17 392b5 9

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Additionally you may want to get managers and their reports count with -  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

[sql] Self Join to get employee manager name

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql