Most efficient way to find something recursively in a table?

Question

I have a table users with the following columns

id INT PRIMARY
username
target
dead

target contains an id of another user in the same table. All users start out with dead as 0. If dead is 1, then that means they're dead. As time goes on, dead can change, but target column will always stay with whatever it started with.

If a user dies, their target becomes whoever killed them's new target. So if I'm trying to find the current target of user A, I would have to first find their original target, and if that target is dead, move onto that target's target, and so on, until I find one that's not dead. and that would be the current target of user A.

Currently I have this query that just gives me the original target

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

But have no idea how to add to it to give me the current target.

Answer 1

Recursivity is used more effectively with "représentation intervallaire", take a look on http://sqlpro.developpez.com/cours/arborescence/.