sql query for hierarchical tree in sql server 2008

Question

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 26-Jun-13 23:17pm

Umashankar Yadav

Updated 27-Jun-13 0:38am

v2

Add a Solution

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

2 solutions

Solution 1

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.

Posted 26-Jun-13 23:47pm

Anupam Singh

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Umashankar Yadav · Accepted Answer · 2013-06-27T03:34:00

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)