Recursive CTE only returns top level

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 only returns top level

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-27 : 12:07:06

Hi

I'm trying to write a recursive CTE on the following table so that I can create a structure of the hierarchy that exists there. The problem is that my CTE is only returning the top level (e.g. where parentID IS NULL) and misses out all the sub-levels.

Can anyone show me what I'm doing wrong please??


DECLARE @tmp TABLE 
	(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[parentID] [int] NULL,
	[SubjectName] [nvarchar](200) NOT NULL
	);

INSERT INTO @tmp (parentID, SubjectName) VALUES (NULL, 'ASP.NET');
INSERT INTO @tmp (parentID, SubjectName) VALUES (NULL, 'SQL Server');
INSERT INTO @tmp (parentID, SubjectName) VALUES (1, 'GridViews');
INSERT INTO @tmp (parentID, SubjectName) VALUES (1, 'DataSets');
INSERT INTO @tmp (parentID, SubjectName) VALUES (1, 'DataTables');
INSERT INTO @tmp (parentID, SubjectName) VALUES (2, 'CTEs');
INSERT INTO @tmp (parentID, SubjectName) VALUES (2, 'Table Variables');

WITH Topics (ID, ParentID, SubjectName, Level)
AS
(
SELECT
	e.ParentID,
	e.ID,
	e.SubjectName,
	0 AS Level
FROM
	@tmp AS e
WHERE
	ParentID IS NULL
UNION ALL
SELECT
	e.ParentID,
	e.ID,
	e.SubjectName, 
	Level + 1
FROM
	@tmp AS e INNER JOIN
	Topics AS t ON e.ParentID = t.ID
)
SELECT 
	ID,
	ParentID,
	SubjectName,
	Level
FROM
	Topics

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 12:09:31

Both
SELECT
e.ParentID,
e.ID,

s.b.
SELECT
e.ID,
e.ParentID,

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2011-05-27 : 12:39:25

Superb - that's fixed it. Thank you very much.

Could you please tell me why the order of columns makes a difference in a CTE expression? I've not seen this effect before when doing normal SELECT's.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-27 : 13:01:34

The names don't make a difference.
Bit like a union statement - it gets the names from the first select and uses the order for the rest.

In a cte you don't have to define the names on the cte declaration it can usually get them from the anchor member (not always though).

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources