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

Note 1: You can preserve the tree order just by adding the ordering condition.

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

Note 2: You can change the ordering of the siblings by changing the ORDER BY condition like above.