hi gurus
have a employee table which consist of the normal employee related
fields which the fields empid and supervisor mainly related to this
question.
have to create a single (the question is whether it is possible) or a
stored procedure to get the result as follow. the input parameter is
empid.
have to retrieve the subordinates records of the employee id passed as
parameter and the drill down.
for example from the below table. if empid 12 is been passed as the
parameter value then it should retrieve the records with following
empid - 1, 26. the employee with empid 1 has subordinates under him
which should also be pulled that is 16, 62 and so on. the output for
the above example will be a result with records in the following order
- 12, 1,16,62, 26 and so on.
hope i have explained the problem clearly
would appreciate if some one can guide in creating an efficient self
join or a stored procedure
EmpId FirstName LastName Supervisor
----- --------- -------- ----------
1 Carl Hogans 12
12 Fred Smith NULL
16 Sue Bankers 1
26 Frank Green 12
55 Karen Feeders NULL
56 James Black 55
57 Kirk Simmons 56
58 Cliff Page 56
59 Jimmy Plant 56
60 Jack Cale 59
61 Robert Santana NULL
62 Jack Russell 1
where clause or parameter passed is 'EmpId'
thanx in advance
bala
