MySQL 8.0

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

create table users(id int, refferal int, balance int);
insert into users values (1, 0, 1000), (2, 1, 1200), (3, 0, 1300), (4, 2, 1000),
(5, 4, 1100), (6, 0, 1700), (7, 6, 2200), (8, 6, 2000), (9, 8, 1000),
(10, 9, 1200);
select * from users;


id
refferal
balance


1
0
1000

2
1
1200

3
0
1300

4
2
1000

5
4
1100

6
0
1700

7
6
2200

8
6
2000

9
8
1000

10
9
1200


with recursive u as
(select t.id, t.refferal, t.balance from users t where refferal = 1
union
select t.id, t.refferal, t.balance from u inner join users t
on u.id = t.refferal)
(select u.id,u.refferal,u.balance from u)


id
refferal
balance


2
1
1200

4
2
1000

5
4
1100

id	refferal	balance
1	0	1000
2	1	1200
3	0	1300
4	2	1000
5	4	1100
6	0	1700
7	6	2200
8	6	2000
9	8	1000
10	9	1200