1

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.

Michael Pakhantsov
  • 24,855
  • 6
  • 60
  • 59
fent
  • 17,861
  • 15
  • 87
  • 91
  • Interesting question - may be tough to do in mySQL alone. What language/platform are you doing this from? – Pekka Oct 11 '10 at 08:20
  • PHP. I know I can just add another column `current_target` and update it every time someone dies. But I want to know if it's possible not to and still only use one query. – fent Oct 11 '10 at 08:21

1 Answers1

0

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

MatTheCat
  • 18,071
  • 6
  • 54
  • 69