The only problem I have with that solution method is that you can't traverse in both directions. IE: Pulling Invoice 3 won't get you back to 1 (or vice versa) depending on the parenting.

The biggest problem with this recursion is that it's self-joining. For those curious:

--am trying to write a query against a table of invoices, returning all relevant rows for a particular InvoiceId. Heres some sample data:

--Create temp table to hold the dummy data

if object_id('tempdb..#IDs') is not null

drop table #IDs

create table #IDs (

InvoiceId int not null

,BookingId int not null

) on [PRIMARY]

go

alter table #IDs add constraint PK_IDs primary key clustered (

InvoiceId

,BookingId

)

with (

STATISTICS_NORECOMPUTE = off

,IGNORE_DUP_KEY = off

,ALLOW_ROW_LOCKS = on

,ALLOW_PAGE_LOCKS = on

) on [PRIMARY]

go

insert #IDs ( InvoiceId, BookingId)

select * from (values (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)) data(InvoiceId,BookingId)

select * from #IDs

DECLARE @InvoiceID INT

SET @InvoiceID = 1

;WITH rCTE AS

(SELECT

InvoiceID,

BookingID,

1 AS HierarchyLevel

FROM

#IDs

WHERE

InvoiceID = @InvoiceID

UNION ALL

SELECT

#IDs.InvoiceID,

ids2.BookingID,

rCTE.HierarchyLevel + 1 AS HierarchyLevel

FROM

rCTE

JOIN

#IDs

ON#IDs.BookingID = rCTE.BookingID

JOIN

#IDs AS ids2

ON#IDs.InvoiceID = ids2.InvoiceID

WHERE

rCTE.HierarchyLevel + 1

)

SELECT * FROM rCTE

I'm trying to work out a way to exclude previously included invoices from the detection list but you can't double-reference the CTE in the recursion, so NOT IN (SELECT) clauses and the like are out. I'm actually thinking this may need to be looped as a baseline and then worked on from there.