Recursive CTE Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Recursive CTE Issue

Author

Topic

Imukai
Starting Member

29 Posts

Posted - 2008-12-02 : 11:10:37

I'm running into an issue with a recursive CTE giving me odd results when combined with an outer join.

Here's a test case to demonstrate what I'm talking about:

DECLARE @Items table (
	ItemID int,
	ParentID int null
);

DECLARE @SideTable table (
	SideTableID int,
	ItemID int
);

INSERT INTO @Items VALUES (1,NULL);
INSERT INTO @Items VALUES (2,NULL);
INSERT INTO @Items VALUES (3,NULL);
INSERT INTO @Items VALUES (4,1);
INSERT INTO @Items VALUES (5,1);
INSERT INTO @Items VALUES (6,4);
INSERT INTO @Items VALUES (7,6);
INSERT INTO @Items VALUES (8,6);

INSERT INTO @SideTable VALUES (100,1);
INSERT INTO @SideTable VALUES (101,6);
INSERT INTO @SideTable VALUES (102,8);

WITH CTE_Items (ItemID, ParentItemID, SideTableID, HierarchyLevel) AS
(
  SELECT A.ItemID, A.ParentID, B.SideTableID, 1 AS HierarchyLevel
  FROM @Items AS A
  LEFT JOIN @SideTable AS B ON A.ItemID = B.ItemID
  WHERE A.ParentID IS NULL
  
  UNION ALL
  
  SELECT A.ItemID, A.ParentID, CTE.SideTableID, (CTE.HierarchyLevel + 1) AS HierarchyLevel
  FROM @Items AS A
  INNER JOIN CTE_Items AS CTE ON A.ParentID = CTE.ItemID
)
SELECT * FROM CTE_Items;

This is the result set:

ItemID  ParentItemID  SideTableID  HierarchyLevel
1       NULL          100          1
2       NULL          NULL         1
3       NULL          NULL         1
4       1             100          2
5       1             100          2
6       4             100          3
7       6             100          4
8       6             100          4

Notice how all of the children of ItemID 1 are showing the SideTableID of ItemID 1? That's not right.

The DESIRED result set is this:

ItemID  ParentItemID  SideTableID  HierarchyLevel
1       NULL          100          1
2       NULL          NULL         1
3       NULL          NULL         1
4       1             NULL         2
5       1             NULL         2
6       4             101          3
7       6             NULL         4
8       6             102          4

I tried modifying the recursive part of the CTE to this:

  SELECT A.ItemID, A.ParentID, B.SideTableID, (CTE.HierarchyLevel + 1) AS HierarchyLevel
  FROM @Items AS A
  LEFT JOIN @SideTable AS B ON A.ItemID = B.ItemID
  INNER JOIN CTE_Items AS CTE ON A.ParentID = CTE.ItemID

But this generates an error:

Msg 462, Level 16, State 1, Line 24
Outer join is not allowed in the recursive part of a recursive common table expression 'CTE_Items'.

Is there something I'm missing? In the past I'd do this by pulling in the entire result set in code and using some recursive functions to build my hierarchy.

This is really the first time I'm messing with CTE so it's quite possible I'm missing something simple, or this task is simply not possible with CTE.

Help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 11:15:06

then do it outside like below


;WITH CTE_Items (ItemID, ParentItemID,HierarchyLevel) AS
(
  SELECT A.ItemID, A.ParentID, 1 AS HierarchyLevel
  FROM @Items AS A
  WHERE A.ParentID IS NULL
  
  UNION ALL
  
  SELECT A.ItemID, A.ParentID,(CTE.HierarchyLevel + 1) AS HierarchyLevel
  FROM @Items AS A
  INNER JOIN CTE_Items AS CTE ON A.ParentID = CTE.ItemID
)
SELECT C.ItemID,  
C.ParentItemID,  
B.SideTableID,  
C.HierarchyLevel
FROM CTE_Items C
LEFT JOIN @SideTable AS B 
ON C.ItemID = B.ItemID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 11:17:41

[code];WITH Yak (ItemID, ParentItemID, HierarchyLevel)
AS (
SELECT ItemID,
ParentID,
1
FROM @Items
WHERE ParentID IS NULL

UNION ALL

SELECT i.ItemID,
i.ParentID,
y.HierarchyLevel + 1
FROM Yak AS y
INNER JOIN @Items AS i ON i.ParentID = y.ItemID
)

SELECT y.ItemID,
y.ParentItemID,
st.SideTableID,
y.HierarchyLevel
FROM Yak AS y
LEFT JOIN @SideTable AS st ON st.ItemID = y.ItemID[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 11:18:08