This is NOT pretty, but it IS functional.

--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

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

drop table #TempStore

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,

@Rowcount INT

SELECT @InvoiceID = 1,

@Rowcount = 1

CREATE TABLE #TempStore

(InvoiceID INT, BookingID INT)

INSERT INTO #TempStore

SELECT

InvoiceID, BookingID

FROM

#IDs

WHERE

InvoiceID = @InvoiceID

-- Set this here, might as well not hit the loop if no records to work from.

SELECT @Rowcount = @@ROWCOUNT

WHILE @Rowcount <> 0

BEGIN

INSERT INTO #TempStore

SELECT

ids2.InvoiceID, ids2.BookingID

FROM

#IDs

JOIN

(SELECT DISTINCT BookingID FROM #TempStore) AS drv

ON#IDs.BookingID = drv.BookingID

JOIN

#IDs AS ids2

ON#IDs.InvoiceID = ids2.InvoiceID

WHERE

#IDs.InvoiceID NOT IN(SELECT DISTINCT InvoiceID FROM #TempStore)

SET @Rowcount = @@ROWCOUNT

END

SELECT * FROM #TempStore