title: CTE Query Not Returning Desired Results
body: <p>I'm not getting the first record below returned in my CTE query (shown later): </p>
  
  <p>Here's my table:</p>
  
  <pre><code>Key  ParentID  ChildID  (Removed DateJoined Field here)
  1       0        1  
  3       1       83
  4       1       84  
  6       83      85  
  7       85      86  
  8       83      87
  </code></pre>
  
  <p>My CTE Query produces the following results:</p>
  
  <pre><code>ID  Name            Date Joined  Parent ID  Parent Name        Level
  83  Hanks, James    2014-09-13      1       Golko, Richard       1
  84  Hanks, James    2014-09-13      1       Golko, Richard       1
  85  Walker, Jamie   2014-09-13      83      Hanks, James         2
  87  Newman, Betty   2014-09-20      83      Hanks, James         2
  86  Adams, Ken      2014-09-13      85      Walker, Jamie        3
  </code></pre>
  
  <p>How can i also return the first record with <code>ParentID = 0</code>?</p>
  
  <p>When I call the following sproc like this: </p>
  
  <pre><code>EXEC UCU_RTG_ProgramStructure_GetMemberTree 0,4
  </code></pre>
  
  <p>I still only get results starting with parentID=1 as shown above</p>
  
  <p>Here's my CTE Query:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[UCU_RTG_ProgramStructure_GetMemberTree]
          @ParentID int,
          @MaxLevel int
  AS
  
  WITH matrix
  AS 
  ( 
  --initialization 
  SELECT UserID, DateJoined, ParentID, 1 AS lvl
  FROM dbo.UCU_RTG_ProgramStructure
  WHERE ParentID = @ParentID
  UNION ALL 
  --recursive execution 
  SELECT p.UserID,p.DateJoined,p.ParentID, lvl+1
  FROM dbo.UCU_RTG_ProgramStructure p INNER JOIN matrix m  
  ON p.ParentID = m.UserID 
  WHERE lvl &lt; @MaxLevel
  ) 
  SELECT matrix.UserID, u.LastName + ', ' + u.FirstName AS Member ,DateJoined,ParentID,u2.LastName + ', ' + u2.FirstName AS Parent,lvl
  FROM matrix
  INNER JOIN dbo.Users u
  ON u.UserID = matrix.UserID
  INNER JOIN dbo.Users u2
  ON u2.UserID = matrix.ParentID
  
  ORDER BY ParentID
  </code></pre>
  
  <p>THE CTE Query is fine except it doesn't return the parentID=0 record(s)</p>
  
  <p>Thanks...</p>