我有下表的值
- 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);
现在我希望结果如下
- ID EmpName BossName
- 1 Abhijit Haris
- 2 Haris Sanal
所以我写了以下查询.
- select E1.ID,E1.EmpName,E.EmpName as BossName from #tmpEmployee E inner join #tmpEmployee E1 on E1.EmpBossID=E.ID.
但问题是第3名员工(Sanal)没有老板.
所以我想要这个确切的结果：
- ID EmpName BossName
- 1 Abhijit Haris
- 2 Haris Sanal
- 3 Sanal Null
我该怎么办？
解决方法
使用正确加入
- select E1.ID,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
我觉得对你好
