SQL Recursive in Same Table to Find Parent

Question

How can I find the parentId of all my items (mySQL)? (there are potentially an infinite amount of levels of hierarchy ... not the best design, but its what I'm working with)

ID      PARENTID    NAME
9146    NULL        thing1
9147    NULL        thing2
9148    9146        thing3
9149    9148        thing4
9150    NULL        thing5
9151    9149        thing6
9152    9147        thing7

The output should then be:

ID     REAL_PARENTID
9146   NULL (or self 9146)
9147   NULL (or self 9147)
9148   9146
9149   9146
9150   NULL (or self 9150)
9151   9146
9152   9147

Answer 1

I actually scripted tree traversal using MySQL Stored Procedure Programming.

I answered such a question in the DBA StackExchange back in Oct 24, 2011. It also included sample data.

Have fun with it.