Q:

CTE Query Not Returning Desired Results

I'm not getting the first record below returned in my CTE query (shown later): 
Here's my table:
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

My CTE Query produces the following results:
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

How can i also return the first record with ParentID = 0?
When I call the following sproc like this: 
EXEC UCU_RTG_ProgramStructure_GetMemberTree 0,4

I still only get results starting with parentID=1 as shown above
Here's my CTE Query:
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 < @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

THE CTE Query is fine except it doesn't return the parentID=0 record(s)
Thanks...

A:

I figured it out finally after looking at my post to make sure it was correct:  the final select clause is wrong:
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

the last INNER JOIN has to be changed to LEFT JOIN because there is no UserID 0 to join the ParentID 0 to.  
Hope this helps someone else with recursive CTE queries.

