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.

btraas · Accepted Answer · 2019-06-26 04:07:51Z

1

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

answered Jun 26, 2019 at 4:07

btraas

1483 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql result table joining multiple SELECT results inside a while loop

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related