Simplest way to do a recursive self-join?

Question

What is the simplest way of doing a recursive self-join in SQL Server? I have a table like this:

PersonID | Initials | ParentID 1          CJ         NULL 2          EB         1 3          MB         1 4          SW         2 5          YT         NULL 6          IS         5 

And 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'm a bit stuck on this can 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.

Thanks! Chris.

Answer 1

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.