The most efficient way to find something recursively in a table?

Question

I have a users table with the following columns

 id INT PRIMARY username target dead 

target contains the identifier of another user in the same table. All users start from the dead as 0. If the dead is 1, then they are dead. Over time, the dead can change, but the target column will always remain where it started.

If the user dies, their target becomes the one who killed their new target. Therefore, if I try to find the current goal of user A, I will have to first find the original goal, and if this goal is dead, go to that target and so on until I find one that is not dead. and this will be the current goal of user A.

I currently have this request that just gives me the original goal

 SELECT `a`.`username`, `a`.`dead`, `b`.`username` FROM `users` AS `a` LEFT JOIN (`users` AS `b`) ON (`a`.`target` = `b`.`id`) 

But I don’t know how to add to it to give me the current goal.

Answer 1

Recursiveness is used more efficiently with "intervallaire représentation", see http://sqlpro.developpez.com/cours/arborescence/ .