Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Write a self join query?

Tags:

I have the following table with values

CREATE TABLE #tmpEmployee(ID int, EmpName varchar(50), EmpBossID int)

insert into #tmpEmployee values ( 1, 'Abhijit', 2);
insert into #tmpEmployee values ( 2, 'Haris', 3);
insert into #tmpEmployee values ( 3, 'Sanal', 0);

Now I want the result become following

ID  EmpName BossName
1   Abhijit Haris
2   Haris   Sanal

so I have written the following query.

select E1.ID,E1.EmpName, E.EmpName as BossName from #tmpEmployee E inner join #tmpEmployee E1 on E1.EmpBossID=E.ID.

But the problem is the 3rd employee (Sanal) has no boss. So I want this exact result:

ID  EmpName BossName
1   Abhijit Haris
2   Haris   Sanal
3   Sanal   Null

What should I do?