SQL Server 2012

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

CREATE TABLE ASSOSIATETABLE
(
	id INT,
	name varchar(10),
	ManagerID int
)
CREATE TABLE Registration 
(
	id INT,
	Number_of_Family_Members int
)
insert into ASSOSIATETABLE values
(1,'A',Null)
,
(2,'B',1),
(3,'C',2),
(4,'D',3),
(5,'E',3),
(6,'F',3),
(7,'G',Null),
(8,'H',7)
8 rows affected

insert into Registration values
(3  ,3)        ,
(4 ,2       ),
(6,2)
3 rows affected

 
 ; WITH recursive_CTE (id,name,MANAGERID,CEONAME,CEO)
 AS(
 SELECT  ID,name,MANAGERID,name ,ID FROM ASSOSIATETABLE WHERE MANAGERID IS NULL
	UNION  ALL
 SELECT B.ID,B.name,B.MANAGERID,A.CEONAME,A.CEO FROM recursive_CTE  A
 INNER JOIN ASSOSIATETABLE  B ON A.ID=B.ManagerID 
 )
 
 SELECT * FROM recursive_CTE
 WHERE ID NOT IN (SELECT ID FROM Registration ) 
 AND MANAGERID IS NOT NULL


id
name
MANAGERID
CEONAME
CEO


8
H
7
G
7

2
B
1
A
1

5
E
3
A
1