Find Top level Parent and With Recursive CTE

debugcn Published at Dev

InTheWorldOfCodingApplications

I have got two tables as following

 Table Person

  Id   Name
  1    A
  2    B
  3    C
  4    D
  5    E

Table RelationHierarchy

ParentId   CHildId
    2         1
    3         2
    4         3

This will form a tree like structure

I am using following CTE to find top level parent

  DECLARE @childID INT 
  SET @childID  = 1 --chield to search

  ;WITH RCTE AS
  (
    SELECT *, 1 AS Lvl FROM RelationHierarchy 
    WHERE ChildID = @childID

    UNION ALL

    SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
    )
   SELECT TOP 1 id, Name
   FROM RCTE r
   inner JOIN dbo.Person p ON p.id = r.ParentId
   ORDER BY lvl DESC

Now the problem is , incase the Person is himself a toplevel then his Id should be returned instead of no records. Do i need a temp table or table variable to check count of CTE and then return the @childID if Count is Zero?

Gordon Linoff

I think this solves your problem. The idea is to force the start on the child rather than doing a lookup initially:

WITH RCTE AS
  (
    SELECT @childID as parentId, NULL as childid, 1 AS Lvl

    UNION ALL

    SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
    )
   SELECT TOP 1 id, Name
   FROM RCTE r
   inner JOIN dbo.Person p ON p.id = r.ParentId
   ORDER BY lvl DESC;

Collected from the Internet

Please contact [email protected] to delete if infringement.