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', 1),
(2, '2023-10-15', 2),
(3, '2023-11-05', 1),
(4, '2023-11-20', 3),
(5, '2023-12-01', 2);

select * from #invoices


id
Billingdate
customerid


1
2023-10-01
1

2
2023-10-15
2

3
2023-11-05
1

4
2023-11-20
3

5
2023-12-01
2


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


id
Billingdate
customerid
id
namename
referredby


1
2023-10-01
1
1
John
null

2
2023-10-15
2
2
Jane
null

3
2023-11-05
1
1
John
null

4
2023-11-20
3
3
Anne
2

5
2023-12-01
2
2
Jane
null


SELECT i.id, i.Billingdate, c.namename as CustomerName, c.referredby , r.namename as referral_name
  FROM #Invoices i
LEFT JOIN #Customers2 c ON i.customerid=c.id
LEFT JOIN #Customers2 r ON c.referredby=r.id


id
Billingdate
CustomerName
referredby
referral_name


1
2023-10-01
John
null
null

2
2023-10-15
Jane
null
null

3
2023-11-05
John
null
null

4
2023-11-20
Anne
2
Jane

5
2023-12-01
Jane
null
null

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

id	Billingdate	customerid	id	namename	referredby
1	2023-10-01	1	1	John	null
2	2023-10-15	2	2	Jane	null
3	2023-11-05	1	1	John	null
4	2023-11-20	3	3	Anne	2
5	2023-12-01	2	2	Jane	null