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

SELECT *
FROM (
VALUES
(1,9),
(1,10),
(1,11),
(2,11),
(3,11),
(3,12),
(3,13)) x
([InvoiceId],[BookingId])

--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,
	primary key clustered (InvoiceId, BookingId)
);

INSERT #IDs VALUES (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)
GO

DECLARE @val int = 1;

WITH x AS
(
	SELECT i.InvoiceId, i.BookingId 
	from 
	(
		SELECT InvoiceId, BookingId 
		from #IDs
		WHERE InvoiceId = @val
	) a
	JOIN #IDs i ON a.BookingId = i.BookingId
)
SELECT DISTINCT i.* 
FROM x
RIGHT JOIN #IDs i ON x.InvoiceId = i.InvoiceId
WHERE x.InvoiceId IS NOT NULL

CREATE TABLE #IDsParents (
InvoiceId int not null
,BookingId int not null
,ParentInvoiceID INT
)

insert #IDsParents ( 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)


;WITH Parent AS(
SELECT a.InvoiceId, b.InvoiceId AS Parent
  FROM #IDsParents a
  LEFT JOIN #IDsParents b
    ON a.BookingId = b.BookingId
   AND a.InvoiceId > b.InvoiceId
WHERE b.InvoiceId IS NOT NULL
)

UPDATE b SET ParentInvoiceID = a.Parent
  FROM Parent a
  RIGHT JOIN #IDsParents b
    ON a.InvoiceId = b.InvoiceId

SELECT * FROM #IDsParents

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

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

INSERT #IDs VALUES (1,9), (1,10), (1,11), (2,11), (2,12), (3,12), (3,13), (4,14), (5,14)
GO