Simplest way to do a recursive self-join?

hierarchical-data, recursion, self-join, sql, sql-server

Solution

WITH    q AS 
        (
        SELECT  *
        FROM    mytable
        WHERE   ParentID IS NULL -- this condition defines the ultimate ancestors in your chain, change it as appropriate
        UNION ALL
        SELECT  m.*
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q

By adding the ordering condition, you can preserve the tree order:

WITH    q AS 
        (
        SELECT  m.*, CAST(ROW_NUMBER() OVER (ORDER BY m.PersonId) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN AS bc
        FROM    mytable m
        WHERE   ParentID IS NULL
        UNION ALL
        SELECT  m.*,  q.bc + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY m.ParentID ORDER BY m.PersonID) AS VARCHAR(MAX)) COLLATE Latin1_General_BIN
        FROM    mytable m
        JOIN    q
        ON      m.parentID = q.PersonID
        )
SELECT  *
FROM    q
ORDER BY
        bc

By changing the `ORDER BY` condition you can change the ordering of the siblings.

Problem

What is the simplest way of doing a recursive self-join in SQL Server? ``` PersonID | Initials | ParentID 1 CJ NULL 2 EB 1 3 MB 1 4 SW 2 5 YT NULL 6 IS 5 ``` I want to be able to get the records only related to a hierarchy starting with a specific person. So If I requested CJ's hierarchy by PersonID=1 I would get: ``` PersonID | Initials | ParentID 1 CJ NULL 2 EB 1 3 MB 1 4 SW 2 ``` And for EB's I'd get: ``` PersonID | Initials | ParentID 2 EB 1 4 SW 2 ``` I can't think how to do it apart from a fixed-depth response based on a bunch of joins. This would do as it happens because we won't have many levels but I would like to do it properly.

Original source

Related problems