SQL tree traversal down to the root and back up one

Question

I have the following table (ObjectStates) and want to get the root as well as the first-child of the root:

ID    Title    ParentID
1     Draft    null
2     Green    null
3     Red      null
4     Foo      1
5     Bar      4
6     Some1    1
7     Some2    6
8     XYZ      2
9     Some3    7

I would like the following output:

GetState(5)
-- returns root: 1, first-child: 4
GetState(6)
-- returns root: 1, first-child: 6
GetState(7)
-- returns root: 1, first-child: 6
GetState(9)
-- returns root: 1, first-child: 6
GetState(8)
-- returns root: 2, first-child: 8

So no matter how deep in the hierarchy I am querying - I always want the root element as well as the first child element. If you would consider this tree, I always want the red and blue elements, no matter how deep in the tree I am.

I can get the "root" states like so:

WITH CTEHierarchy
AS (
    SELECT 
    ID
        ,0 AS LEVEL
        ,ID AS root

    FROM ObjectStates
    WHERE  ParentID IS NULL

    UNION ALL

    SELECT 
    ObjectStates.ID
        ,LEVEL + 1 AS LEVEL
        ,[root]

    FROM ObjectStates
    INNER JOIN CTEHierarchy uh ON uh.id = ObjectStates.ParentID
    )    
    SELECT [root]
    FROM CTEHierarchy
    WHERE ID = @ObjectStateID 

Which gives me my desired root results:

GetState(5)
-- returns root: 1
GetState(9)
-- returns root: 1
GetState(2)
-- returns root: 2

How can I traverse up from there? So get the next child in the tree from root? Or the other way around - get the root as well as the first level. Recursion is breaking my head.

Answer 1

I think you'll need to walk up the hierarchy first, then get the top two levels:

WITH cteHierarchy As
(
   SELECT
      ID,
      ParentID,
      0 As Level
   FROM
      ObjectStates
   WHERE
      ID = @ObjectStateID

   UNION ALL

   SELECT
      OS.ID,
      OS.ParentID,
      H.Level + 1
   FROM
      cteHierarchy As H
      INNER JOIN ObjectStates As OS
      ON H.ParentID = OS.ID
),
cteReveresedHierarchy As
(
   SELECT
      ID,
      ROW_NUMBER() OVER (ORDER BY Level DESC) As RowNumber
   FROM
      cteHierarchy
)
SELECT
   ID
FROM
   cteReveresedHierarchy
WHERE
   RowNumber In (1, 2)
;

EDIT
To get the two items in one row:

If you could guarantee that you were never going to start at the root, you could change the filter to WHERE RowNumber = 2, and include the ParentID column. However, if you ever started at the root, you'd only have one row, so that query wouldn't work.

To allow for the query to start at the root, you'll need to take row 2, if it exists, or row 1 otherwise:

WITH cteHierarchy As
(
   SELECT
      ID,
      ParentID,
      0 As Level
   FROM
      ObjectStates
   WHERE
      ID = @ObjectStateID

   UNION ALL

   SELECT
      OS.ID,
      OS.ParentID,
      H.Level + 1
   FROM
      cteHierarchy As H
      INNER JOIN ObjectStates As OS
      ON H.ParentID = OS.ID
),
cteReveresedHierarchy As
(
   SELECT
      ID,
      ParentID,
      ROW_NUMBER() OVER (ORDER BY Level DESC) As RowNumber
   FROM
      cteHierarchy
)
SELECT TOP 1
   ParentID As [root]
   ID As [FirstChild]
FROM
   cteReveresedHierarchy
WHERE
   RowNumber In (1, 2)
ORDER BY
   RowNumber DESC
;

Answer 2

I was playing with this query for traversing primary key foreign key relation ship today to track whole path and this problem seems similar. So just pasting the same code. You can directly run this code and check if this is what you need.

This query adds two more column in CTE i.e. Hops and Path where Hops is the level of element and Path is the nodes traversed from start to end.

WITH cte
AS
(
    SELECT 
        fk.create_date
        , fk.modify_date
        , fkc.constraint_object_id AS ConstraintId
        , OBJECT_NAME(fkc.constraint_object_id) AS ConstraintName
        , OBJECT_NAME(fkc.referenced_object_id) AS PrimaryKeyTableName
        , rc.name AS PrimaryKeyColumnName
        , OBJECT_NAME(fk.parent_object_id) AS ForeignKeyTableName
        , lc.name AS ForeignKeyColumnName
    FROM sys.foreign_key_columns fkc
    INNER JOIN sys.columns rc 
        ON  rc.OBJECT_ID = fkc.referenced_object_id 
        AND fkc.referenced_column_id = rc.column_id
    INNER JOIN sys.foreign_keys fk 
        ON  fk.OBJECT_ID = fkc.constraint_object_id
    INNER JOIN sys.columns lc 
        ON  lc.OBJECT_ID = fk.parent_object_id
        AND fkc.parent_column_id = lc.column_id
)
, cte2(create_date, modify_date, ConstraintName
        , PrimaryKeyTableName, PrimaryKeyColumnName
        , ForeignKeyTableName, ForeignKeyColumnName
        , Hops, path ) AS 
    (
        SELECT
            create_date, modify_date, ConstraintName
            , PrimaryKeyTableName, PrimaryKeyColumnName
            , ForeignKeyTableName, ForeignKeyColumnName 
            , 1 , CAST(QUOTENAME(PrimaryKeyTableName + '.' + PrimaryKeyColumnName) AS VARCHAR(4000))
        FROM cte
    UNION ALL
        SELECT 
            cte.create_date, cte.modify_date, cte.ConstraintName
            , cte.PrimaryKeyTableName, cte.PrimaryKeyColumnName
            , cte.ForeignKeyTableName, cte.ForeignKeyColumnName
            , cte2.Hops +1, CAST(cte2.path + '-> ' +QUOTENAME(cte.PrimaryKeyTableName+ '.' + cte.PrimaryKeyColumnName) AS VARCHAR(4000))
        FROM cte2 INNER JOIN cte ON cte2.ForeignKeyTableName = cte.PrimaryKeyTableName
        AND cte2.ForeignKeyColumnName != cte.PrimaryKeyColumnName
    )
SELECT 
ConstraintName
        , PrimaryKeyTableName, PrimaryKeyColumnName
        , ForeignKeyTableName, ForeignKeyColumnName
        , Hops, path + '-> ' + QUOTENAME(ForeignKeyTableName + '.' + ForeignKeyColumnName) AS Path
FROM cte2

This query works only if foreign key constraints are present physically in database.