get all employees those directly or indirectly reports to an employee with hierarchy level

Question

0.00/5 (No votes)

See more:

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-

SQL

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.........

Posted 25-Apr-13 23:05pm

Sukanta Ghosh

Updated 25-Apr-13 23:21pm

CHill60

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2013-04-25T23:16:00

The exact approach will depend on which version of SQL you have but in general you need to do some research around recursive queries ... http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL[^]

The Microsoft definition can be found here http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx[^].

I tend to find MS documentation great if you already know the subject but not so hot when trying to grasp new concepts so have a look at this guy's primer ...http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/[^]

Sukanta Ghosh · Accepted Answer · 2013-04-26T04:21:00

With CTE as ( 
Select emp_id,emp_Name,reports_to,1 as Level From emp Where reports_to=1 
Union all 
Select E.emp_id,E.emp_name,E.reports_to,Level+1 
From emp as E Inner join CTE on E.reports_to=CTE.emp_id ) 
Select * from CTE;

Quote:
Any way thanks though, for helping about exactly what to look for

get all employees those directly or indirectly reports to an employee with hierarchy level

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

get all employees those directly or indirectly reports to an employee with hierarchy level

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us