FACED IN ONE OF A MNC IN Sozhinganallur [Chennai]
GIVEN INPUT
Emp_id |
Emp_MgrId |
Emp_Name |
1 |
NULL |
A |
2 |
1 |
B |
3 |
1 |
C |
4 |
2 |
D |
EXPECTED OUTPUT
Emp_Name |
Emp_MgrNam |
A |
NULL |
B |
A |
C |
A |
D |
B |
Creating Temp table
- create table #emp(Emp_id int,Emp_mgrid int,Emp_Name char(1))
- insert into #emp values(1,NULL,'A')
- insert into #emp values(2,1,'B')
- insert into #emp values(3,1,'C')
- insert into #emp values(4,2,'D')
After data insertion into the Temp table
- select * from #emp order by emp_id
Emp_Id |
Emp_MgrId |
Emp_Name |
1 |
NULL |
A |
2 |
1 |
B |
3 |
1 |
C |
4 |
2 |
D |
Case 1
- select b.Emp_Name ,a.Emp_Name as Emp_MgrName from #emp a right join #emp b on b.Emp_mgrid=a.emp_id order bya.Emp_Name
Emp_Name |
Emp_MgrName |
A |
NULL |
B |
A |
C |
A |
D |
B |
Case 2
- select a.Emp_Name ,b.Emp_Name as Emp_MgrName from #emp b right join #emp a on a.Emp_mgrid=b.emp_id order bya.Emp_Name
Emp_Name |
Emp_MgrName |
A |
NULL |
B |
A |
C |
A |
D |
B |
Case 3
- select a.Emp_Name ,b.Emp_Name as Emp_MgrName from #emp b right join #emp a on b.emp_id = a.Emp_mgrid order bya.Emp_Name
Emp_Name |
Emp_MgrName |
A |
NULL |
B |
A |
C |
A |
D |
B |
Case 4
- select b.Emp_Name ,a.Emp_Name as Emp_MgrName from #emp b left join #emp a on a.emp_id = b.Emp_mgrid order bya.Emp_Name
Emp_Name |
Emp_MgrName |
A |
NULL |
B |
A |
C |
A |
D |
B |