Get all employee who directly or indirectly reports to an employee, with hierarchy level no

foreign-keys, iteration, primary-key, sql, sql-server

Solution

You could use a recursive CTE:

; with  CTE as 
        (
        select  emp_id
        ,       reports_to
        ,       emp_name
        ,       1 as level
        from    Emp
        where   emp_name = 'Sumanta'
        union all
        select  child.emp_id
        ,       child.reports_to
        ,       child.emp_name
        ,       level + 1
        from    Emp child
        join    CTE parent
        on      child.reports_to = parent.emp_id
        )
select  *
from    CTE

Example at SQL Fiddle.

Problem

I have a Employee table like ``` emp_id bigint, reports_to bigint, emp_name varchar(20), Constraint [PK_Emp] Primary key (emp_id), Constraint [FK_Emp] Foreign key (reports_to) references [MSS].[dbo].[Emp]([emp_id]) emp_id reports_to emp_name ------ ------ -------------- 1 null Sumanta 2 1 Arpita 3 null Pradip 4 1 Sujon 5 2 Arpan 6 5 Jayanti ``` I want to get all the employees that directly or indirectly reports to Sumanta or emp_id(1), and with hierarchy level, like this: ``` emp_id hierarchy_level emp_name ------ --------------- ---------- 2 1 Arpita 4 1 Sujon 5 2 Arpan 6 3 Jayanti ``` I am new to SQL and just couldn't find what to use or how to get those results. Is it worth a stored procedure with table valued variable, or just a Tsql select query will be enough. Any help is most welcome. All I have done is- ``` Select Ep.emp_id,ep.emp_eame From Emp as E Inner Join Emp as Ep on Ep.reports_to=E.Emp_id Where E.reports_to=1 or E.emp_id=1; ``` but this is accurate upto 2 level and I cant even generate the hierarchy_level no. Any suggestion, idea............ will be most helpfull.........

Original source