title: Recursive query to find the parent record
body: <blockquote>
    <p>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</p>
    
    <p>For ex:</p>
    
    <p>To find the childrecord for 71:</p>
    
    <p>level4 parent - 154, level3 parent - 192, level2 parent - 209 
      or level1 child - 209</p>
    
    <p>209 is the needed answer.</p>
    
    <p>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.</p>
    
    <p>Can we do this easily in recursive CTE?</p>
  </blockquote>
  
  <p><code></p>
  
  <pre><code>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
  </code></pre>
  
  <p>-- I tried to with recursive CTE</p>
  
  <pre>-- 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
  </code></pre>