|
Home > Archive > Microsoft SQL Server forum > August 2005 > self join or stored procedure
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
self join or stored procedure
|
|
|
| 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
| |
| --CELKO-- 2005-08-03, 3:23 am |
| Look up the nested set model or get a copy of TREES & HIERARCHY IN SQL.
| |
| Erland Sommarskog 2005-08-03, 3:23 am |
| bala (balkiir@gmail.com) writes:
> 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.
In SQL 2000 you will have to write a stored procedure that recurses
through the hierarchy. You may find this section in Books Online
helpful: Access and Changing Relational Data / Advanded Query Concepts /
Transact-SQL Tips / Expanding Hierarchies.
In SQL 2005, currently in beta, there is a new T-SQL construct that
permits you to do this in a single query.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
|
| thanx --celko--
i am looking to the hierachial query from the example in books online
which deals with the worlds - states and cities.
was wondering whether there are any other approach.
thanx again
bala
| |
|
| thanx erland, much appreciated.
bala
|
|
|
|
|