SQL recursive CTE query, odd results set SQL Server 2005

Question

I am trying to write a recursive CTE query in SQL Server 2005 but am getting an odd set of results. My table is:

PairID  ChildID ParentID
900        1        2       
901        2        3       
902        3        4       

This is my CTE Query:

WITH TESTER (PairID, 
             ChildID, 
             ParentID, 
             Level)
AS (SELECT a.PairID, a.ChildID,a.ParentID, 0 AS Level
    FROM BusinessHierarchy AS a
    UNION ALL
    SELECT b.PairID, b.ChildID, b.ParentID, oh.Level + 1 AS Level
    FROM BusinessHierarchy AS b INNER JOIN
    TESTER AS oh ON b.ChildID =  oh.ParentID)
SELECT 
      x.PairID, 
      x.ChildID,
      x.ParentID,
      x.Level
 FROM TESTER AS x
 ORDER BY x.Level, x.ChildID, x.ParentID

Ok, so I am now getting a dataset return, however, it is not as expected in that it contains repetition in the following manner:

PairID  ChildID ParentID Level
900     1       2        0
901     2       3        0
902     3       4        0
...

900     2       3        1
901     3       4        1
...

900     3       4        2

If someone could explain to me why this is happening and how I would could correct it I would be very grateful.

As far as my last question goes, how would I have to modify it to display the initial childID with each of the parents like this:

Original
PairID  ChildID ParentID Level
900     1       2        0
901     2       3        1
902     3       4        2

I want it displayed as:
PairID  ChildID ParentID Level
900     1       2        0
901     1       3        1
902     1       4        2

Answer 1

Your problem is your starting point. The first part of the CTE query returns all rows (notice their level is all 0).

Then - Your next part of the query will go and get all the related rows (which adds to your earlier resultset).

I did notice, however, that you have no record for the id of 4

if you did, here is what you would do:

;WITH TESTER (PairID, ChildID, ParentID, Level) AS (
    SELECT
        a.PairID,
        a.ChildID,
        a.ParentID,
        0 AS Level
    FROM BusinessHierarchy AS a
    LEFT JOIN BusinessHierarchy a2 ON a.ParentID = a2.ChildID
    WHERE a2.PairID is null

    UNION ALL

    SELECT
        b.PairID,
        b.ChildID,
        b.ParentID,
        oh.Level + 1 AS Level
    FROM BusinessHierarchy AS b
    INNER JOIN TESTER AS oh ON b.ParentID =  oh.ChildID
)

SELECT 
    x.PairID, 
    x.ChildID,
    x.ParentID,
    x.Level
FROM TESTER AS x
ORDER BY x.Level, x.ChildID, x.ParentID

Also, see my answer to a similar question to show the correct sort order (using the path)