title: Get all parents in parent child hierarchy
body: <p>I have the following data in my table</p>
  
  <pre><code>ID          NAME                           PARENTID    STATUS
  ----------- ------------------------------ ----------- ------
  1           Folder A                       0           0
  2           Folder B                       1           0
  3           Folder C                       2           1
  4           Folder D                       1           0
  5           Folder E                       4           0
  6           Folder G                       5           0
  7           Folder H                       6           1
  </code></pre>
  
  <p>Above records are from table [@Temp].</p>
  
  <pre><code>[Name] - Folder Name
  [ID] - Unique ID of folder in database (identity)
  [ParentID] - Represents the parent of current folder.
  </code></pre>
  
  <p>Query to populate above data in the table:</p>
  
  <pre><code>  DECLARE @Temp TABLE
      (
        [ID] INT IDENTITY(1, 1) ,
        [NAME] VARCHAR(30) ,
        [PARENTID] INT,
        [STATUS] BIT
      )
  
    INSERT    INTO @Temp
              SELECT  'Folder A' ,
                      0, 0
              UNION
              SELECT  'Folder B' ,
                      1, 0
              UNION
              SELECT  'Folder C' ,
                      2, 1
              UNION
              SELECT  'Folder D' ,
                      1, 0
              UNION
              SELECT  'Folder E' ,
                      4, 0
              UNION
              SELECT  'Folder G' ,
                      5, 0
              UNION
              SELECT  'Folder H' ,
                      6, 1              
  </code></pre>
  
  <p>I am having the following query to get records where status=1                                          </p>
  
  <pre><code>  SELECT    *
    FROM      @Temp WHERE [STATUS]=1      
  </code></pre>
  
  <p>which gives the following output</p>
  
  <pre><code>ID          NAME                           PARENTID    STATUS
  ----------- ------------------------------ ----------- ------
  3           Folder C                       2           1
  7           Folder H                       6           1
  </code></pre>
  
  <p><strong>My goal is to fetch those records too which are the parents (till parentid=0)</strong> of the records came with the above query. i.e I want to get this output which contains the parents of Folder C and Folder H: </p>
  
  <pre><code>ID          NAME                           PARENTID    STATUS
  ----------- ------------------------------ ----------- ------
  1           Folder A                       0           0
  2           Folder B                       1           0
  3           Folder C                       2           1
  4           Folder D                       1           0
  5           Folder E                       4           0
  6           Folder G                       5           0
  7           Folder H                       6           1
  </code></pre>