Click here to Skip to main content
11,802,762 members (68,893 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 28-Dec-10 23:36pm
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!!!
Rate this: bad
Please Sign up or sign in to vote.

Solution 4

declare @empid int;
 set @empid = 7;
 with cte_report
 ( select empcode,managercode from [Test].[dbo].[Emp_manager]  where empcode = @empid
 union all
 select e.empcode,e.managercode from  [Test].[dbo].[Emp_manager]   as e
 inner join cte_report as m
 on e.empcode = m.managercode )
 select managercode from cte_report  order by managercode asc
Jörgen Andersson at 28-Jun-15 14:38pm
Why do you wake up a five year old question that HAS an accepted answer?

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

  Print Answers RSS
0 KrunalRohit 366
1 OriginalGriff 365
2 F-ES Sitecore 295
3 Afzaal Ahmad Zeeshan 230
4 CPallini 230
0 OriginalGriff 2,950
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,695
4 Richard MacCutchan 1,157

Advertise | Privacy | Mobile
Web01 | 2.8.151002.1 | Last Updated 28 Jun 2015
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