Click here to Skip to main content
Rate this: bad
good
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 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
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 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
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 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
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 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 350
1 Jochen Arndt 150
2 PIEBALDconsult 110
3 Richard MacCutchan 105
4 Sergey Alexandrovich Kryukov 104
0 OriginalGriff 6,055
1 DamithSL 4,621
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,500
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 30 Dec 2010
Copyright © CodeProject, 1999-2014
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