Self Join to get employee and their manager name from single table

This is very much important query for interview. Remember ‘Self join’ is not a keyword in sql. It is a concept of join (Inner/Outer) in same table once again.

Here we have a table called ‘EmpDtl’

id    Name       Deptid   Managerid

-----------------------------------------------

1        Pritam Das   1             0
2        Rintu Das     1             1
3        Lisa Das       2             1
4        Piyali Das     2             2
5        Subhadip Das2           1
6         Anurag Das    2          2

Here is the query:

select E1.id,E1.Name as EmpName,E1.DeptID,isnull(E2.Name,’CEO’) as ManName from EmpDtl E1 left join EmpDtl E2 ON E1.ManagerID=E2.id

Here i have used ‘EmpDtl’ table 2 times by using 2 different extensions (E1,E2).
The mejor point of this query is ‘ON E1.ManagerID=E2.id’.
Means it maches Managerid from E1 with id from E2.
Here i have used Left join to get the unmacthed record (like Managerid=Null)

Output:
id     EmpName          Deptid     ManName

--------------------------------------------------------------

1       Pritam Das            1             CEO
2       Rintu Das              1             Pritam Das
3       Lisa Das                2             Pritam Das
4       Piyali Das              2             Rintu Das
5       Subhadip Das       2             Pritam Das
6       Anurag D              2              Rintu Das