How Do I Recursively Query A Table?

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