Help with Recursive SQL

Question

I have a category table similar to this:

   uid | ParentLevel | ParentID | Name
   ------------------------------------
    1  |       0     |      0   |  foo
    2  |       1     |      1   |  blat
    3  |       1     |      1   |  baz
    4  |       2     |      3   |  blah
    5  |       0     |      0   |  bar

I am trying to get output similar to this:

1 | foo
2 | foo | blat
3 | foo | baz
4 | foo | baz | blah
5 | bar

And so on. CTE's are out as this is SQL 2000. I have tried something like this, but can't seem to get it quite right:

SELECT     c1.uid, c1.Name, c2.Name AS Expr1, c3.Name AS Expr2
FROM         dbo.Categories AS c1 INNER JOIN
                  dbo.Categories AS c2 ON c1.uid = c2.ParentID INNER JOIN
                  dbo.Categories AS c3 ON c2.uid = c3.ParentID
WHERE     (c1.ParentLevel = 0) AND (c2.ParentLevel = 1) AND (c3.ParentLevel = 2)

This would only return records if there is a third level which isn't always true. Is there a way to do this?

Answer 1

Use LEFT JOIN

SELECT     c1.uid, c1.Name, c2.Name AS Expr1, c3.Name AS Expr2
FROM         dbo.Categories AS c1 LEFT JOIN 
                  dbo.Categories AS c2 ON c1.uid = c2.ParentID LEFT JOIN 
                  dbo.Categories AS c3 ON c2.uid = c3.ParentID
WHERE     (c1.ParentLevel = 0) AND (c2.ParentLevel = 1) AND (c3.ParentLevel = 2)

Answer 2

Use LEFT JOIN instead of INNER JOIN and move conditions on level from WHERE to JOIN conditions

SELECT      c1.uid, c1.Name, c2.Name AS Expr1, c3.Name AS Expr2
FROM        dbo.Categories AS c1 
LEFT JOIN   dbo.Categories AS c2 
        ON  c1.uid = c2.ParentID
        AND c2.ParentLevel = 1
LEFT JOIN   dbo.Categories AS c3 
        ON  c2.uid = c3.ParentID
        AND c3.ParentLevel = 2
WHERE       c1.ParentLevel = 0

Answer 3

LEFT JOIN will help you to get the expected result. INNER JOIN merges only the records which matches from other query result.