Wednesday, September 14, 2011

Using SQL self join Find Upper level manager in Employee table

Posted by Rahul Kharde at 12:20 AM
A self-join is a query in which a table is joined (compared) to itself. Self-joins are used to compare values in a column with other values in the same column in the same table.
You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table. The most common case where you'd use a self-join is when you have a table that references itself

I have the employee detail table, In that i want to find the manager for each Employee.
The employee name and manager column are present in same table
Consider the following Employee table

CREATE TABLE Employee
(
    Emp_Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Emp_Name VARCHAR(100),
    Manager_Id INT NULL
)

INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('a',null)
INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('b',1)
INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('c',2)
INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('d',3)
INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('e',4)
INSERT INTO Employee(Emp_Name,Manager_Id)VALUES('f',5)

Emp_Id
Emp_Name
Manager_Id
1
a
Null
2
b
1
3
c
2
4
d
3
5
e
4
6
f
5

To find the manager name for each employee in the Employee table

SELECT e2.Emp_Id,
      e2.Emp_Name as EMP_NAME,
      e1.Emp_Name as MANAGER_NAME
FROM Employee e1,Employee e2
WHERE e1.Emp_Id=e2.Manager_Id

OUTPUT
Emp_Id
EMP_NAME
Manager_NAME
2
b
a
3
c
b
4
d
c
5
e
d
6
f
e

Now find the Employee Manager Name and its senior Manager Name

SELECT e2.Emp_Id,
         e2.Emp_Name AS EMP_NAME,
         e1.Emp_Name AS MANAGER_NAME,
         e3.Emp_Name AS SENIOR_MANAGER_NAME
FROM Employee e1, Employee e2, Employee e3
WHERE e1.Emp_Id= e2.Manager_Id
AND e1.Manager_Id = e3.Emp_Id
ORDER BY e2.Emp_Id

OUTPUT

Emp_Id
EMP_NAME
MANAGER_NAME
SENIOR_MANAGER_NAME
3
c
b
a
4
d
c
b
5
e
d
c
6
f
e
d



If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

1 comments:

Joe on September 14, 2013 at 11:19 PM said...

Very nice one Rahul, thanks for sharing and taking effort to write this.

Good page here too:

How self join works


Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top