Hello Salman,

Just a clarification on the second report, do you want all subordinates or
just the immediate ones, i.e. 100 has subordinate 10 or 100 has 
subordinates 10,1. Neither of these reports by the way shows the "real" 
hierarchy, example...

100 has subordinate 10 who has a subordinates 1 and 2 etc..
1 has a superior 10 who has a superior 100

If you want to explore the hierarchy, since you're using 2000 we might
consider a XML recordset or you'll need to consider how you want to
represent this "structure" in a recordset.

Also, the two reports are really different so one procedure would be two
cumbersome to maintain so I suggest 3 procedures with the following
interfaces...

spGetOrgList( intID IN, intType IN, intLevel IN, intErrCode OUT )
// Single entry point, use intType to
// differentiate between list of bosses
// or list of subordinates

spGetSuperiorList( intID IN, intErrCode OUT )
spGetSubordinateList( intID IN, intLevel IN, intErrCode OUT )
// intLevel restricts the nesting level and defaults to 1 or
// just immediate subordinates

John

> -----Original Message-----
> Subject: Re: self join
> From: salman_ahmad@h...
> Date: Tue, 28 Aug 2001 17:55:36
> X-Message-Number: 2
>
> Thanks for the reply,
>
> All I want is that if I pass the employee no. I should get
> the list of
> boses and when I pass the boss no. I should get all the
> employees under
> him.
>
> I believe it is going to be a recurrsive operation, I am
> using SQL 2000.
>
> 0 means the boss has no superior. You can suggest a better
> layout of the
> table as I am still in the design phase. One solution I was
> thinking of
> was to store level of hierarchy in the table too, but it's
> not practical.
>
> Salman.
>
> > I need to write a recurrsive query
> >
> > e.g. I have a table with two columns employee and boss
> >
> > employee    boss
> > 1           10
> > 10          100
> > 100         0
> >
> > This shows 1 report to 10, 10 report to 100 and 100
> doesn't report to
> > anyone.
> >
> > If I need to find a list of bosses for 1 how would I write that?
> > The input should be employee no. 1 and output should be
> boss no. 10, 100
> >
> > Thanks,
> > Salman A. Sheikh