Query to know the child parent

Transact-SQL
1

I have employee database with team leader now I have to generate the query which help me to extract the list of employee under team leader. There are chances if I pass the ID for whom there are two teams so this person should get all the employees under them including both the team leaders.

E1 - T1
E2 - T1
E3 - T2
T1 - T3
so if I pass T1 we should get two employees and when we pass T3 we should get three employees

Please suggest

2

Could this be what you're looking for?

with cte(team_leader,employee)
  as (select team_leader
            ,employee
        from yourtable
      union all
      select b.team_leader
            ,a.employee
        from yourtable as a
             inner join cte as b
                     on b.employee=a.team_leader
     )
select *
  from cte
 where team_leader='T3'
;
3

If your real data resembles that structure then, technically, you're screwed (even if it doesn't look like it yet) because it doesn't obey the "laws" of a simple parent/child hierarchy. All team leaders are also employees and should be included in the left column (the child column in this case) even if then have to have a NULL in the right (parent) column).

4

Try this sample query it may be helpful to you.

WITH CTE 
AS(
SELECT ID,Name,ManagerID, 1 RecursiveCallNumber  FROM Employee  WHERE ID=2
UNION ALL
SELECT  E.ID,E.Name,E.ManagerID,RecursiveCallNumber+1 RecursiveCallNumber  FROM Employee E
INNER JOIN CTE ON E.ManagerID=CTE.ID)
SELECT * FROM CTE