1

How can I fix this code to MySQL 5.7?

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)

Fiddle

halfer
  • 19,824
  • 17
  • 99
  • 186
Pisit
  • 31
  • 4
  • One workaround is to use the [now obsolete] variables, but it's not fool proof. The other solution is to write a stored procedure. – The Impaler Dec 29 '21 at 14:33

1 Answers1

2

Inspired by this answer:

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))

Fiddle

Zakaria
  • 4,715
  • 2
  • 5
  • 31
  • Thank you for your answer but this query if refferal < id will return to null [link]https://dbfiddle.uk/?rdbms=mysql_5.6&fiddle=02f2141ddacc84f1dc197d68ac67ed3c – Pisit Dec 29 '21 at 15:05
  • @Pisit it returns the same result as your other query! [fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=02f2141ddacc84f1dc197d68ac67ed3c) – Zakaria Dec 29 '21 at 15:08
  • Do you have a way to return results without this condition? (refferal is greater or less than id) – Pisit Dec 29 '21 at 15:22
  • @Pisit maybe what you're looking for (in your recursive query) is `where t.id = 10` instead of `where t.refferal = 10` ? Since there are no users with refferal = 10... – Zakaria Dec 29 '21 at 15:32