Possible Duplicate:
Recursive function in sql server 2005?
How do you perform an iterative query on a table? I have a simple table which consists of a:
KeyField, childID, parentID
Starting with a childID, I want to pull the parentID and then query again to see if that parent (which is now the child) has its own parent, working up through the complete hierarchy, how do I do that?
Microsoft SQL Server, version number 09.00.3042
In SQL Server 2005 and later, you are best to use a recursive CTE (common table expression) for this sort of query. (in SQL 2000 and earlier you were limited to using a recursive stored procedure).
Something like the following is what you need:
WITH ParentChildRels (ParentId, ChildId, KeyField, HierarchyLevel) AS
(
-- Base case
SELECT
ParentId,
ChildId,
KeyField,
1 as HierarchyLevel
FROM Records
WHERE ChildId = @ChildId
UNION ALL
-- Recursive step
SELECT
r.ParentId,
r.ChildId,
r.KeyField,
pr.HierarchyLevel + 1 AS HierarchyLevel
FROM Records r
INNER JOIN ParentChildRels pr ON
r.ParentId = pr.ParentId
)
SELECT *
FROM ParentChildRels
ORDER BY HierarchyLevel, ParentId, ChildId, KeyField
