SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table #customers2(
id int,
namename varchar(20),
referredby int
)


INSERT INTO #customers2  VALUES
(1, 'John', NULL),
(2, 'Jane', NULL),
(3, 'Anne', 2),
(4, 'Eric', NULL),
(5, 'Pat', 1); 

select * from #customers2


id
namename
referredby


1
John
null

2
Jane
null

3
Anne
2

4
Eric
null

5
Pat
1


create table #invoices(
id	int,
Billingdate	date,
customerid	int);

INSERT INTO #invoices (id, Billingdate, customerid) VALUES
(1, '2023-10-01', 101),
(2, '2023-10-15', 102),
(3, '2023-11-05', 101),
(4, '2023-11-20', 103),
(5, '2023-12-01', 102);

select * from #invoices


id
Billingdate
customerid


1
2023-10-01
101

2
2023-10-15
102

3
2023-11-05
101

4
2023-11-20
103

5
2023-12-01
102


SELECT * FROM
#Invoices i
LEFT JOIN #Customers2 c on i.customerid=c.id


id
Billingdate
customerid
id
namename
referredby


1
2023-10-01
101
null
null
null

2
2023-10-15
102
null
null
null

3
2023-11-05
101
null
null
null

4
2023-11-20
103
null
null
null

5
2023-12-01
102
null
null
null

id	Billingdate	customerid
1	2023-10-01	101
2	2023-10-15	102
3	2023-11-05	101
4	2023-11-20	103
5	2023-12-01	102

id	Billingdate	customerid	id	namename	referredby
1	2023-10-01	101	null	null	null
2	2023-10-15	102	null	null	null
3	2023-11-05	101	null	null	null
4	2023-11-20	103	null	null	null
5	2023-12-01	102	null	null	null