SQL recursive query

Question

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

Answer 1

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