CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Tippu
Tippu

Reputation: 1261

Self Join to get employee manager name

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]

Upvotes: 11

Views: 229749

Answers (15)

Shubham Darunte
Shubham Darunte

Reputation: 1

select distinct( e1.id), e1.emp_name from employee as e1 
inner join employee as e2 on e1.id = e2.manager_id;

Upvotes: 0

Yaqub Ahmad
Yaqub Ahmad

Reputation: 27659

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

Upvotes: 22

Yash
Yash

Reputation: 1

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

Upvotes: 0

Harish H.N
Harish H.N

Reputation: 21

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

Upvotes: 1

shailendra1118
shailendra1118

Reputation: 71

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;

Upvotes: 0

Zahir Ansari
Zahir Ansari

Reputation: 21

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

Upvotes: 2

Arunsai
Arunsai

Reputation: 11

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

Upvotes: 0

niketan
niketan

Reputation: 41

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

Upvotes: 4

TechPerceptron
TechPerceptron

Reputation: 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)

Upvotes: 0

Prince Jea
Prince Jea

Reputation: 5680

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

Upvotes: 2

Jirka Hanika
Jirka Hanika

Reputation: 13529

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.

Upvotes: 4

PoeHaH
PoeHaH

Reputation: 1936

   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 :)

Upvotes: 8

dan radu
dan radu

Reputation: 2782

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

Upvotes: 3

Shailesh
Shailesh

Reputation: 1218

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

Upvotes: 3

Joe G Joseph
Joe G Joseph

Reputation: 24076

    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

Upvotes: 3

Related Questions