Click here to Skip to main content
11,411,840 members (66,462 online)
Rate this: bad
Please Sign up or sign in to vote.
Hi All,
I've a table whose schema and sample data are as follows:

EmpCode               ManagerCode
=========             =============
1                        1
2                        1
3                        2
4                        2
5                        3
6                        2
7                        3
8                        4
The name of the table is EMP

Now my task is to create a query which will give a hierarchy of manager against a particular EmpCode. Say for example for EmpCode 7 the output will be

I've tried with self join but its giving only the immediate manager not the total hierarchy. It will be of great help if provided with any clue to achieve this result
Posted 29-Dec-10 0:36am
Hiren Solanki at 29-Dec-10 6:30am
Reply : so it will return 1,2,3,4 ? do you want it to be different. ?
senguptaamlan at 29-Dec-10 6:33am
can be depending upon the empID...1,2,3,4 are not static data...they are given just for an example.
Hiren Solanki at 29-Dec-10 6:33am
I've got the problem actually, It needs some extra efforts.
Hiren Solanki at 31-Dec-10 0:51am
You could read the 'story behind' of that TIP, and you could vote also if you liked it.
Hiren Solanki at 30-Dec-10 8:21am
see my updated answer, I've written a complete trip for you on the otherside.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

What about THIS

select distinct managerid from EMP where managerid <= (select managerid from EMP where empid=7)

You could try building Recursive CTE[^] for data hirarchy.

See my newly written TIP[^] only for you.
Hiren Solanki at 29-Dec-10 6:32am
Okey I got the problem actually now, Let me try it.
senguptaamlan at 29-Dec-10 6:28am
if I give empID = 8 ?
senguptaamlan at 31-Dec-10 0:46am
@Hiren thanks man....
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Maybe the following article will help you :[^]

senguptaamlan at 29-Dec-10 5:58am
yeah provides a good solution, but breaks down my present DB Structure if I'm gonna use the depth, Lineage columns...
Dylan Morley at 29-Dec-10 5:54am
good article
Rate this: bad
Please Sign up or sign in to vote.

Solution 3


create procedure testemp(@empid int)
declare @temp int;
create table #temp (mgrid int,);
select @temp=mgrid  from empmgr  where empid =@empid;
insert into  #temp (mgrid)  values (@temp);
while (@temp != @empid)
select @temp=mgrid , @empid =empid  from empmgr where empid =@temp;
insert into  #temp (mgrid)  values (@temp);
select * from #temp;

CREATE TABLE [dbo].[empmgr](
    [empid] [int] NULL,
    [mgrid] [int] NULL
Marcus Kramer at 29-Dec-10 16:23pm
My vote of 1.... WTF!!!

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

  Print Answers RSS
0 OriginalGriff 268
1 Sergey Alexandrovich Kryukov 200
2 Sascha Lefévre 170
3 ProgramFOX 130
4 Maciej Los 110
0 Sergey Alexandrovich Kryukov 8,955
1 OriginalGriff 7,158
2 Maciej Los 3,480
3 Abhinav S 3,248
4 Peter Leow 3,059

Advertise | Privacy | Mobile
Web04 | 2.8.150414.5 | Last Updated 30 Dec 2010
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100