Click here to Skip to main content
14,770,678 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello All
I Have a Table With Structure

Employee_Id int
EmpName Varchar(500)
SeniorId in

The Data Like This


Employee_Id EmpName SeniorId
1 A 0
2 B 1
3 C 2
4 D 3
5 E 0
6 F 5
7 G 6

I want all the Employees Under Any Any Employee in a hierarchical manner
For Ex. Under A -> B,C,D
Under B -> C,D
Under E -> F,G

Please Help me in Writing Sql Query in Sql Server.

Thanks
Posted
Updated 27-Jun-13 1:38am
v2
Comments
[no name] 27-Jun-13 5:37am
   
How B,C,D coming under A dear??
I thing it should be under A ->A,E

any ways chill:
your solution :

select t2.Employee_Id as Employee, t1.name as Senior_Name from Employees t1 left join Employees t2 on t1.Employee_Id=t2.Senior

result will be

Employee_id Senoir
1 B

as per your requirement.
   
Comments
Umashankar Yadav 27-Jun-13 6:06am
   
Sir,it will give all the data with the senior name but i have to refine it.
Suppose i have interned Employee_Id=1 then Result Should be B,C,D and if Employee_Id=2 then Result Should be C,D
[no name] 27-Jun-13 6:11am
   
hmm.. get this done in your front end logic.
you can use list,array,hash tables .etc for the same....
Umashankar Yadav 27-Jun-13 6:18am
   
i am able to refine it up lo one level by providing t2.Employee_Id, But i need data in Sub-tree format.
Umashankar Yadav 27-Jun-13 7:10am
   
Help Plz
[no name] 27-Jun-13 7:23am
   
first of all bind your data in List.
then use foreach (for each Senoir) .Then you can display it anywhere.
and further you have to apply your sense coding..
Because its not clear that where you want to display it.
Thanks To All For Helping Me. Here is a Code To Get Employee Connected Directly or Indirectly by a Senior.

with  CTE as ( select  Employee_Id     
        from    Employee
        where   SeniorId  = @Employee_Id
        union all
        select  child.Employee_Id       
       
        from    Employee child
        join    CTE parent
        on      child.SeniorId = parent.Employee_Id
        )
Select * from Emplyee Where Employee_Id in (select  * from    CTE)
   

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