3

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
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
monkey-wrench
  • 1,556
  • 2
  • 14
  • 16
  • Do you need to get up to the top parent, or the parent one-up in the hierarchy? Nevermind, I see you need to get to the top. – BD. Feb 16 '12 at 17:17
  • 1
    The answer to this question: http://stackoverflow.com/questions/3704130/recursive-mysql-query should be of use to you. –  Feb 16 '12 at 17:20
  • Your example is unclear. From the data posted, what you are looking for seems to be *just* SELECT ID, PARENTID from table, that's all. What is the problem ? – Marc Alff Feb 16 '12 at 17:28
  • @Marc Not exactly, look at items IDs 9149 and 9151. The REAL_PARENTID is differnt – monkey-wrench Feb 16 '12 at 17:41
  • Oh, missed it. Thanks for the clarification. – Marc Alff Feb 16 '12 at 18:18
  • 1
    @monkey-wrench can you design it such that when any first level child insert occurs, it will find its last ancestor and save it in a `oldest parent` column? That way for any subsequent child insertion, you could just find their immediate parent and copy their `oldest parent` column. – cctan Feb 17 '12 at 01:12

2 Answers2

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.

Community
  • 1
  • 1
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
0

You can do this easily using SQL CTE tables (Recursive Queries)

Check this for learning how to do it:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Alex
  • 5,971
  • 11
  • 42
  • 80
  • 2
    It's mysql... :) http://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql – Asken Feb 16 '12 at 17:24
  • Here is manual for MariaDB: https://mariadb.com/kb/en/recursive-common-table-expressions-overview/ – Lovor Oct 29 '22 at 23:07