title: Querying hierarchical data and getting all relationships up the chain
body: <p>I have parent/child hierarchical data that looks like:</p>
  
  <pre><code>AgencyId  ParentAgencyId
  6220        NULL
  6221        6220
  6222        6221
  6223        6221
  6224        6223
  6219        6220
  6225        NULL
  </code></pre>
  
  <p>I am trying to query to crawl the hierarchy to match all relationships and set a "level" similar to the below.</p>
  
  <pre><code>Parent    Child     Level
  6220        6220        0
  6220        6221        1
  6220        6222        2
  6220        6223        2
  6220        6224        3
  6220        6219        1
  6221        6222        1
  6221        6223        1
  6221        6224        2
  6223        6224        1
  6225        6225        0
  </code></pre>
  
  <p>If the Agency doesn't have a parent, it would have a "level" of zero.</p>
  
  <p>The code I was trying to get to do this is:</p>
  
  <pre><code>DECLARE @AgencyCount INT
  DECLARE @i INT = 1
  DECLARE @AgencyId INT
  
  IF OBJECT_ID('tempdb..#tmpAgencies') &gt; 0
  DROP TABLE #tmpAgencies
  
  CREATE TABLE #tmpAgencies (ID INT IDENTITY(1,1), AgencyId INT)
  INSERT INTO #tmpAgencies SELECT AgencyId FROM dbo.Agency WHERE CreatorId = 59641 
  
  IF OBJECT_ID('tempdb..#ChildAgencies') &gt; 0
  DROP TABLE #ChildAgencies
  
  CREATE TABLE #ChildAgencies(AgencyId INT, ParentAgencyId INT, IsMultiAgencyOffice BIT,     LevelCount INT)  
  
  SELECT @AgencyCount = COUNT(AgencyId) FROM #tmpAgencies         
  
  WHILE @i &lt; @AgencyCount
  
  BEGIN  
  
      DECLARE @InsertedCount INT
      DECLARE @AgencyLevel INT = 0
  
      SELECT @AgencyId = AgencyId FROM #tmpAgencies AS TA WHERE ID = @i
  
      INSERT INTO #ChildAgencies(AgencyId, ParentAgencyId, IsMultiAgencyOffice, LevelCount)
      SELECT AgencyId, @AgencyId, IsMultiAgencyOffice, @AgencyLevel   
      FROM dbo.Agency
      WHERE AgencyId = @AgencyId
  
      SET @InsertedCount = @@ROWCOUNT
  
      WHILE @InsertedCount &gt; 0
          BEGIN  
  
              SET @InsertedCount = NULL
              SET @AgencyLevel = @AgencyLevel + 1      
  
              INSERT INTO #ChildAgencies(AgencyId, ParentAgencyId, IsMultiAgencyOffice, LevelCount)
              SELECT AgencyId, @AgencyId, IsMultiAgencyOffice, @AgencyLevel
              FROM dbo.Agency AG
              WHERE AgencyId NOT IN (SELECT AgencyId FROM #ChildAgencies)
                  AND ParentAgencyId IN (SELECT AgencyId FROM #ChildAgencies)
                  AND StatusCode &lt;&gt; 109 /*QA-Deleted*/
  
              SET @InsertedCount = @@ROWCOUNT
  
  
          END  
  
  
  
      SET @i = @i + 1
  
  END
  </code></pre>
  
  <p>I borrowed the inner-most loop from something else that does almost what I was looking for. I know loops are frowned upon. I was trying to do this originally with a recursive CTE, but couldn't get it to work. Also, I really hate the sub-queries in the where clause, but I was going to tackle that once I started getting the results I was looking for.</p>
  
  <p>Thanks</p>