title: TSQL recursive query help parent-child
body: <p>I'm trying to build a t-sql query that will return parent and its child accounts in the same table based on a roll-up input. Can someone please assist?  </p>
  
  <pre><code>***TABLE:USERS*** 
   UserId  ParentId  
   66      -1
   67      -1
   68      -1
   69      54
   70      55
   71      56
   72      57
  </code></pre>
  
  <p>This is what I have so far, but it only returns one account, which is the last one and I need to include the ParentId in the recursion so it will pull the list of the parent and the child accounts one after the other. </p>
  
  <pre><code>    DECLARE @RollUp AS bit 
      DECLARE @ParentID As INT 
      SELECT @RollUp = 0 
           If @Rollup = 1 SELECT @ParentID = CASE WHEN ParentID = -1 
           THEN Users.USER_ID 
           Else ParentID END 
      FROM Users WHERE user_Id  IN (70,71) ELSE 
      SELECT @ParentID = Users.USER_ID 
      FROM Users WHERE USER_Id  IN (70,71) ;
      WITH TRAVERSE_TREE_CTE AS 
      (  
      Select User_ID,ParentID 
      FROM Users 
      WHERE User_ID = @ParentID 
      UNION ALL Select Users.User_ID, Users.ParentID 
      FROM TRAVERSE_TREE_CTE INNER 
      JOIN Users 
      ON TRAVERSE_TREE_CTE.User_ID = Users.ParentID
      )
      Select * from Users 
      where User_id IN (SELECT distinct User_ID FROM TRAVERSE_TREE_CTE)
  </code></pre>