Click here to Skip to main content
12,076,925 members (46,449 online)
Rate this:
 
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

EmpCode
=======
1
2
3
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
Comments
Hiren Solanki 29-Dec-10 6:30am
   
Reply : so it will return 1,2,3,4 ? do you want it to be different. ?
senguptaamlan 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 29-Dec-10 6:33am
   
I've got the problem actually, It needs some extra efforts.
Hiren Solanki 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 30-Dec-10 8:21am
   
see my updated answer, I've written a complete trip for you on the otherside.
Rate this: bad
 
good
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.
  Permalink  
v3
Comments
Hiren Solanki 29-Dec-10 6:32am
   
Okey I got the problem actually now, Let me try it.
senguptaamlan 29-Dec-10 6:28am
   
if I give empID = 8 ?
senguptaamlan 31-Dec-10 0:46am
   
@Hiren thanks man....
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Maybe the following article will help you :
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql[^]

Cheers
  Permalink  
Comments
senguptaamlan 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 29-Dec-10 5:54am
   
good article
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Hi,

create procedure testemp(@empid int)
as
begin
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)
begin
select @temp=mgrid , @empid =empid  from empmgr where empid =@temp;
insert into  #temp (mgrid)  values (@temp);
end
select * from #temp;
end

CREATE TABLE [dbo].[empmgr](
    [empid] [int] NULL,
    [mgrid] [int] NULL
) ON [PRIMARY]
  Permalink  
Comments
Marcus Kramer 29-Dec-10 16:23pm
   
My vote of 1.... WTF!!!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

declare @empid int;
 set @empid = 7;
 with cte_report
 as
 ( 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
  Permalink  
Comments
Jörgen Andersson 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160212.1 | Last Updated 28 Jun 2015
Copyright © CodeProject, 1999-2016
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