Even the Team Lead will also report to some other team lead so complexity arises.. and based on condition we have to select team... u have to write the cond'n or logic to check either we have to display the employees that report to that particular Team lead(
Lower Hirearchy) or employees that are working under teamlead's team lead(
Next Upper Hirearchy)....
U can try some thing like this...
If Exists(select 1 from TM_USER where Report_To=@EmployeeId)
Else
Select Emp.TM_UserID,Emp.FullName as EmpName,TL.FullName as TeamLead
From TM_USER Tm
Inner join TM_USER T1 on Tm.Report_To=T1.TM_USERID
Left Join TM_USER Emp on Emp.Report_To=T1.TM_USERID or Emp.TM_UserID=T1.TM_USERID
Inner join TM_USER TL on TL.TM_UserID=Emp.Report_To
Where Tm.TM_UserID =106
Select EMP.TM_UserID,EMP.FullName as Empname,TL.FullName as TeamLead
From TM_USER T1
Inner join TM_USER EMP on T1.TM_UserID=EMP.Report_To or T1.TM_UserID=EMP.TM_userID
Inner join TM_USER TL on EMP.Report_To=TL.TM_UserID
Where T1.TM_UserID =13