| Consider the following table
that shows employees and their immediate supervisors:
| |
EmpID |
Name |
SupervisorID |
| |
1 |
Clark Kent |
|
|
2 |
Bruce Wayne |
1 |
|
3 |
Peter Parker |
1 |
|
4 |
Bruce Banner |
1 |
|
5 |
Robin |
2 |
This table represents a hierarchical relationship. As a supervisor
is also an employee, he or she too has a supervisor to report
to. The chain continues until you reach the root of the hierarchy.
In any such organizational structure, some frequently asked
questions are:
Who are all the supervisors, direct or indirect, for a
specific employee? Does an employee report to a specific supervisor, either
directly or indirectly?
In SQL Server 2000, there was no inherent way to perform
hierarchical queries. SQL Server 2005 supports defining recursive
queries, making it easy to work with hierarchical data.
I will show the recursive query first and then explain the
specifics of the query.
The following recursive query flattens the hierarchy such
that all legal employee-supervisor combinations can be shown:
| |
WITH SupervisorList AS
(
SELECT EmpID, SupervisorID
FROM League
WHERE NOT SupervisorID is NULL
UNION ALL
SELECT A.EmpID, B.SupervisorID
FROM
League AS A
INNER JOIN
SupervisorList AS B
ON
A.SupervisorID = B.EmpID
)
SELECT * FROM SupervisorList
|
Here is the resulting output:
| |
EmpID |
SupervisorID |
| |
2 |
1 |
|
3 |
1 |
|
4 |
1 |
|
5 |
2 |
|
5 |
1 |
In a recursive query, we start with an initial set of data.
We supplement this data with new data. The resulting data
is once again supplemented with more data. The process continues
until all the data is exhausted.
Here is a simplified syntax of a recursive query:
| |
WITH QueryName AS
(
StartingQuery
UNION ALL
QueryWithMoreData
)
Statement that uses QueryName
|
In our case, the starting query is:
| |
SELECT
EmpID, SupervisorID
FROM League
WHERE NOT SupervisorID is NULL |
We join the result with the original table such that the
supervisor-id from the original table match the employee-id
from the resulting data:
| |
SELECT A.EmpID,
B.SupervisorID
FROM
League AS A
INNER JOIN
SupervisorList AS B
ON
A.SupervisorID = B.EmpID |
The intermediate resulting tables as well as the final resulting
table are named SupervisorList. At the end of the recursion,
we simply get the data from this resulting table using our
standard SELECT statement.
Note that, if not properly designed, you may get an infinite
recursion. By default, SQL Server 2005 stops the execution
after 100 recursions. However, a different limit can be specified
as part of the query.
Hope you now have a good idea on building recursive query.
Solving the two questions that were mentioned at the beginning
of the article is left as an exercise for you.
As usual, you can download the corresponding xFusion packs
from xFusion Developers Network website.
|
