How do I recursively query a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How do I recursively query a table?

Author

Topic

edsilv
Starting Member

9 Posts

Posted - 2007-06-28 : 15:30:01

Hi,

I'm building an application where items are part of hierarchical "chain" consisting of chain segments.

Here are the relevant tables.

Chains:
Id (int)
UserId (Guid)

ChainSegments:
Id (int)
ChainId (int - foreign key to Chains table)
ParentId (int - foreign key to Id column in same table)
UserId (Guid)

There are more columns in ChainSegments but they are not relevant in this context.

You can see here that ChainSegments belong to Chains and ChainSegments can have other ChainSegments as children.

I'm trying to write a recursive Sql query that finds a ChainSegment for a given ChainId and recursively selects its "child" ChainSegments for a given number of iterations. Hope this is making sense so far :-)

Here's what I've got:

GO
WITH ChainSegmentTree (Id, ParentId, Level)
AS
(
-- Anchor member definition
SELECT a.Id, a.ParentId,
0 AS Level
FROM ChainSegments AS a
INNER JOIN ChainSegments AS b
ON a.Id = b.ParentId
WHERE a.ParentId IS NULL
UNION ALL
-- Recursive member definition
SELECT a.Id, a.ParentId,
Level + 1
FROM ChainSegments AS a
INNER JOIN ChainSegments AS b
ON a.Id = b.ParentId
INNER JOIN ChainSegmentTree AS c
ON a.Id = c.ParentId
)
-- Statement that executes the CTE
SELECT e.Id, e.ParentId, Level
FROM ChainSegmentTree AS e
INNER JOIN ChainSegments AS f
ON e.Id = f.Id
WHERE Level = 0;
GO

I've based this on an example I found here:
http://technet.microsoft.com/en-us/library/ms186243.aspx

Unfortunately this only returns one row, the "root" ChainSegment (ParentId = null) and nothing else.

I'm admittedly rubbish at Sql and would really appreciate any help that could be offered here.

Many thanks,

Ed

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-28 : 17:53:12

WHERE Level = 0

That is limiting your results to only those that are at level 0. Which, presumably, is only one row..?

edsilv
Starting Member

9 Posts

Posted - 2007-06-28 : 19:08:40

Hi,

Thanks for that. That helped but it was still really broken.
I think I've fluked a nearly-working query but the problem is it doesn't return rows that don't have children. Anything with a child is included in the result. I need this to include ChainSegments even if they don't have any children.

DECLARE @ChainId AS Int
SET @ChainId = 1
DECLARE @SegmentId AS Int
SET @SegmentId = 4;

WITH ChainSegmentTree (Id, ChainId, ParentId, Level)
AS
(
-- Anchor member definition
SELECT DISTINCT a.Id, a.ChainId, a.ParentId, 0 AS Level
FROM ck_ChainSegments AS a
INNER JOIN ck_ChainSegments AS b
ON a.Id = b.ParentId
WHERE a.Id = @SegmentId AND a.ChainId = @ChainId
UNION ALL
-- Recursive member definition
SELECT a.Id, a.ChainId, a.ParentId, Level + 1
FROM ck_ChainSegments AS a
INNER JOIN ChainSegmentTree AS b
ON a.ParentId = b.Id
)
-- Statement that executes the CTE
SELECT Id, ChainId, ParentId, Level
FROM ChainSegmentTree
ORDER BY Level

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-28 : 19:59:24

I can't test this as I do not have any data, but I think this may work to get you started. Then you might be able to add in the other conditions you are trying to impose. But, let's get the base query qorking first:

WITH ChainSegmentTree (Id, ParentId, Level)
AS
(
	-- Anchor member definition
	SELECT 
		a.Id, 
		a.ParentId, 
		0 AS Level
	FROM 
		ChainSegments AS a
	WHERE 
	a.ParentId IS NULL
	
	UNION ALL
	
	-- Recursive member definition
	SELECT 
		a.Id, 
		a.ParentId,
		Level + 1
	FROM 
		ChainSegments AS a
	INNER JOIN 
		ChainSegmentTree AS c
		ON a.Id = c.ParentId
)
-- Statement that executes the CTE
SELECT 
	e.Id, 
	e.ParentId, 
	Level
FROM 
	ChainSegmentTree AS e

edsilv
Starting Member

9 Posts

Posted - 2007-06-28 : 20:10:44

Hi,

Yep, I've done the same thing and it now works perfectly:

DECLARE @ChainId AS Int
SET @ChainId = 1
DECLARE @SegmentId AS Int
SET @SegmentId = 5
DECLARE @Levels AS Int
SET @Levels = 5;

WITH ChainSegmentCTE (Id, ChainId, ParentId, Level)
AS
(
-- Anchor member definition
SELECT a.Id, a.ChainId, a.ParentId, 0 AS Level
FROM ck_ChainSegments AS a
WHERE a.Id = @SegmentId AND a.ChainId = @ChainId
UNION ALL
-- Recursive member definition
SELECT a.Id, a.ChainId, a.ParentId, Level + 1
FROM ck_ChainSegments AS a
INNER JOIN ChainSegmentCTE AS b
ON a.ParentId = b.Id
)
-- Statement that executes the CTE
SELECT Id, ChainId, ParentId, Level
FROM ChainSegmentCTE
WHERE Level <= @Levels
ORDER BY Level

Thanks very much for your help!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-06-29 : 12:53:50

Anytime. Gald you figured it out. :)

bwebb
Starting Member

4 Posts

Posted - 2007-07-20 : 11:14:05

I don't understand the need to set @Levels. Would'nt you want to run until all children have been selected? And if so, does ommitting the final where clause provide this?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-07-20 : 13:29:22

The original requirement was to select only a certian(max) number of levels. So that is why the @Levels is set. If you wanted to return all children for a given set then yes, removing the WHERE clause (Level <= @Levels) would/should return them all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources