Write a self join query?

Question

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?

Replace the `inner join` with [`left outer join`](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join). — Lieven Keersmaekers, Jul 02 '13 at 04:49
select E1.ID,E1.EmpName, E.EmpName as BossName from #tmpEmployee E Left Outer join #tmpEmployee E1 on E1.EmpBossID=E.ID tried but result become ID EmpName BossName NULL NULL Abhijit 1 Abhijit Haris 2 Haris Sanal — Haris N I, Jul 02 '13 at 04:53
@Haris - I didn't look close enough at the expected results and your current statement but the crux is that you should look up the difference between an `inner` and an `outer` join. — Lieven Keersmaekers, Jul 02 '13 at 05:02
possible duplicate of [self join query](http://stackoverflow.com/questions/344098/self-join-query) — Prahalad Gaggar, Jul 02 '13 at 06:35

score 1 · Accepted Answer · answered Jul 02 '13 at 04:56

Use Right Join

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

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

I think its ok for u

score 1 · Answer 2 · edited Feb 13 '14 at 19:03

1

Use a LEFT JOIN and reverse the order of your tables:

select
  E.ID,
  E.EmpName,
  B.EmpName as BossName
from tmpEmployee E
left join tmpEmployee B on E.EmpBossID = B.ID

See a live demo of this query on SQLFiddle

Putting the "employee" part of the join first means that all employees are listed.

Using a left join means that employees without a boss (eg the CEO) will still be listed, but will have a null for the BossName column.

If you truly want only employee listed if they have a boss, change the query to simply JOIN instead of LEFT JOIN (note that the default join type is INNER)

p.s. formatting your query doesn't hurt either:

edited Feb 13 '14 at 19:03

animuson

52,378
28
138
145

answered Jul 02 '13 at 05:00

Bohemian

389,931
88
552
692

Thanks for u r advice . But answer become not correct it getting result as ID EmpName BossName 1 Abhijit NULL 2 Haris Abhijit 3 Sanal Haris – Haris N I Jul 02 '13 at 05:13
Oops! Got the join around the wrong way. I've updated the query and sqlfiddle link. Renaming the table aliases made it clearer too. It works correctly now – Bohemian Jul 02 '13 at 06:31

Vijay · Answer 3 · 2013-07-02T05:05:34.940

0

try out this...

Use Left Join..

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

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

edited Jul 02 '13 at 05:05

answered Jul 02 '13 at 05:00

Vijay

7,651
9
41
69

No that not correct. I am getting answer become ID EmpName BossName NULL NULL Abhijit 1 Abhijit Haris 2 Haris Sanal – Haris N I Jul 02 '13 at 05:03
No that also getting wrong answer. Atleast u make sure that posting cmnt are correct – Jul 02 '13 at 05:09

Write a self join query?

3 Answers3