title: Is there a way to select Parent IDs in SQL without recursion or looping?
body: <p>I have a table with the following columns: group_id, parent_id, name</p>
  
  <p>In this table parent_id is the group_id of another record. There is a 1 to N relationship of parents to children. This forms a hierarchy with only one top level group having NULL for a parent_id. There could be an arbitrary amount of depth, but in practice my hierarchy is never more than 20 levels deep.</p>
  
  <p>I would like to retrieve every ancestor (a parent's parent and so on) of a group with a given group_id. I am concerned about the particular way this is returned.</p>
  
  <p>I am using MS SQL 2005, but I am also interested in solutions using other RDBMSs.</p>
  
  <p>I have found some similar questions, but they all seem to break down to recursion, looping or nested sets. I cannot use nested sets, because I cannot change the data structure. I would like to avoid recursion or looping where possible, or at least understand why it is not possible.</p>
  
  <p>Here are some question I found while researching this:</p>
  
  <p><a href="https://stackoverflow.com/questions/4184114/how-to-select-parent-ids">How to select parent ids</a></p>
  
  <p><a href="https://stackoverflow.com/questions/1323245/sql-recursion-without-recursion">Sql recursion without recursion</a></p>