Mysql result table joining multiple SELECT results inside a while loop

Question

has anyone have an idea of the most efficient way to turn this into a while loop in Mysql?.

select * from 1eg_usuarios where 
        user_id_1eg= (select @ult_anf:= id_anfitrion_1eg from 1eg_usuarios where user_id_1eg=121) #40
        or
        user_id_1eg= (select @ult_anf:= id_anfitrion_1eg from 1eg_usuarios where user_id_1eg=@ult_anf) #13
        or
        user_id_1eg= (select @ult_anf:= id_anfitrion_1eg from 1eg_usuarios where user_id_1eg=@ult_anf) #4
        or
        user_id_1eg= (select @ult_anf:= id_anfitrion_1eg from 1eg_usuarios where user_id_1eg=@ult_anf); #1

Thanks.

score 1 · Accepted Answer · answered Jun 26 '19 at 04:07

Are you trying to query id_anfitron_1eg recursively?

MySQL 8.0 introduced the with clause

 with recursive cte (user_id_1eg, name, id_anfitrion_leg) as (
  select     user_id_1eg, name, id_anfitrion_leg
  from       1eg_usarios
  where      id_anfitrion_leg = 121
  union all
  select     p.user_id_1eg, p.name, p.id_anfitrion_leg

  from       1eg_usarios p
  inner join cte
          on p.id_anfitron_1eg = cte.user_id_1eg
)
select * from cte;

How to create a MySQL hierarchical recursive query

Mysql result table joining multiple SELECT results inside a while loop

1 Answers1