Click here to Skip to main content
15,748,748 members
Please Sign up or sign in to vote.
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-

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.........
Updated 25-Apr-13 23:21pm

The exact approach will depend on which version of SQL you have but in general you need to do some research around recursive queries ...[^]

The Microsoft definition can be found here[^].

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 ...[^]
Share this answer
Sukanta Ghosh 26-Apr-13 10:25am    
I wasn't sure exactly what to look for, where to start...... Thanks CHill60 for these links
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;

Any way thanks though, for helping about exactly what to look for
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900