sql - RECURSIVE query in MySQL similar to PostgreSQL -

i have table want rows recurrsively based on id , upline_id (parent id) fields. has been done in postgresql this:

with recursive downlines(id, number, upline_id, first_name, last_name, level) (   select id, number, upline_id, first_name, last_name, 1   mytable   id = 2061   union   select a.id, a.number, a.upline_id, a.first_name, a.last_name, d.level + 1   mytable a, downlines d   a.upline_id = d.id )  select * downlines 

however new mysql , cannot figure out how convert above query mysql version. found mysql:

select  *    (select * agents order id) products_sorted,         (select @pv := '2061') initialisation   find_in_set(upline_id, @pv) > 0 ,     @pv := concat(@pv, ',', id) 

however results not match, in mysql version many rows missing.

can tell how convert above postgresql query mysql recursive/hierarchical (n/unknown depth) data?

mysql not supports with recursive. round problem can instead use session variable(starting @) hold values in query.

try this:

select a.id, a.number, a.upline_id,           a.first_name, a.last_name,           @level:=@level+1 `level` mytable inner join (     select id, number, upline_id, first_name, last_name     mytable id = 2061 ) d on a.upline_id = d.id  ,(select @level:=0) p ;