Hi

Many thanks for the reply, one thing i did not demonstrate in my sample (sorry, me being stupid) is that the parent or child could appear in either column, which makes it a little different so if i run this:-

DECLARE @TransactionComponents TABLE

(

pkTransactionComponent int IDENTITY(1,1) NOT NULL,

pkTransactionID int NOT NULL,

ComponentID int NULL

)

INSERT INTO @TransactionComponents(pkTransactionID, ComponentID)

SELECT 1,5

UNION SELECT 1,6

UNION SELECT 1,7

UNION SELECT 1,8

UNION SELECT 1,9

UNION SELECT 1,10

UNION SELECT 10,2

UNION SELECT 2,11

UNION SELECT 2,12

UNION SELECT 3,4

UNION SELECT 13,2

DECLARE @Id INT

SELECT @Id = 1

;WITH Hierarchy AS

(

SELECT * FROM @TransactionComponents

WHERE pktransactionid = @Id

UNION ALL

SELECT A.* FROM @TransactionComponents AS A

INNER JOIN Hierarchy AS B ON A.pktransactionid = B.componentid

)

SELECT * FROM Hierarchy

It should return 10 rows (13, 2) but only returns 9, this is where I hit the problem, if I add an extra Union ALL catch these I end up in an infinite loop.

Kind Regards

Paul