Geek Answers Handbook

SQL recursive query

Possible duplicate:
Recursive function in SQL Server 2005?

How do you execute an iterative query on a table? I have a simple table consisting of:

KeyField, childID, parentID

Starting with childID, I want to pull out the parent identifier and then query again to find out if this parent (which is now a child) has its own parent element, working through the full hierarchy, how to do this?

Microsoft SQL Server Version Number 09.00.3042

+4
source share
1 answer

In SQL Server 2005 and later, it is best to use a recursive CTE (generic table expression) for this kind of query. (in SQL 2000 and earlier, you were limited to using a recursive stored procedure).

Something like the following that 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
+5
source

More articles:


All Articles