MySQL 5.6

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)
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'recursive u as
(select t.id, t.refferal, t.balance from users t where refferal =' at line 1

select  id,
        refferal,
        balance
from    (select * from users
         order by refferal, id) u,
        (select @pv := 1) v
where   find_in_set(refferal, @pv)
and     length(@pv := concat(@pv, ',', id))


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