Recursive query to find the parent record

Question

Based on the highest level and for the corresponding childID record i.e. 71 here, I need to go up the level all the way to 1 and get the corresponding childID record i.e. 209

For ex:

To find the childrecord for 71:

level4 parent - 154, level3 parent - 192, level2 parent - 209 or level1 child - 209

209 is the needed answer.

Now the tricky part is that the highest level is variable. My query shown above doesn't work as the level increases to 6 or 7 as I will not know the number of joins needed.

Can we do this easily in recursive CTE?

declare @t table (
 childID int,
 ParentID int,
 level int
)

insert into @t
select 71, 154, 4
union
select 154, 192, 3
union
select 192, 209, 2
union
select 209, 0, 1

select * from @t

select t1.childID, t4.ChildID
from @t t1
inner join
@t t2
on t1.ParentID = t2.childID
inner join
@t t3
on t2.ParentID = t3.childID
inner join
@t t4
on t3.ParentID = t4.childID
and t1.childID = 71

-- I tried to with recursive CTE

-- I need to get 71, 209 but getting 209, 0


  ;with MyCTE as
    (
    select childID, ParentID from @t t1 
    where t1.level = 1
    UNION ALL
    select m.childID, t2.childID from @t t2
    inner join
    MyCTE m
    on m.childID = t2.ParentID
    )
select top 1 * from MyCTE

Answer 1

Try this:

declare @t table (
 childID int,
 ParentID int,
 level int
)

insert into @t
select 71, 154, 4
union
select 154, 192, 3
union
select 192, 209, 2
union
select 209, 0, 1

Declare @SearchChild int
set @SearchChild=71

  ;with MyCTE as (
      select t1.childID, t1.ParentID , @SearchChild AS searchChild, t1.level
        from @t t1 
        where t1.childID = @SearchChild
      UNION ALL
      select t1.childID, t1.ParentID , c.SearchChild, t1.level
        from @t t1
        inner join MyCTE c on t1.childID=c.ParentID
  )
select top 1 * from MyCTE order by level asc

OUTPUT:

childID     ParentID    searchChild level
----------- ----------- ----------- -----------
209         0           71          1

I'm not sure what you are after, there is no row that has 209 and 71 together? this is the best you can do. Also, this CTE works up the chain and not down, and should work much better on large tables.

Answer 2

This is the way to do it:

;with MyCTE as
(
    select childID, ParentID, t1.childID As firstChild, 0 As depth
    from @t t1 
    where t1.level = 1

    UNION ALL

    select t2.childID, t2.ParentID, m.firstChild, m.depth + 1
    from @t t2
    inner join MyCTE m
    on m.childID = t2.ParentID
)
select TOP 1 childID, firstChild  
from MyCTE
ORDER BY depth DESC

Gives you

childId firstChild
71      209