CODEWITHSUNDEEP
sqljoin
Haris N I
Haris N I

Reputation: 6854

Write a self join query?

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?

Upvotes: 8

Views: 210

Answers (3)

Bohemian
Bohemian

Reputation: 425178

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:

Upvotes: 1

Vijay
Vijay

Reputation: 8451

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

Upvotes: 0

user2431384
user2431384

Reputation:

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

Upvotes: 1

Related Questions