StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POget a recursive parent list
text
Body
copied!<p>Using MySQL, I want to return a list of parents, from a table that has a field structure like this. ID,PARENTID,NAME (a standard parent-child hierarchy). I would like to traverse "up" the tree to return a list of ALL 'parents'. </p> <p>I realize that "nested set", might be a better way to handle this - but currently I cannot change the structure of the data. I will look to do that in the future. Currently - my set of data will realistically contain a few levels of depth - nothing crazy... maybe 2-5 so my recursive hit shouldn't be 'too expensive'.</p> <p>I've looked at the solutions presented in <a href="https://stackoverflow.com/questions/1988586/sql-server-get-parent-list">SQL Server get parent list</a> - but this syntax bombs in mySQL...</p> <p>Does anyone have an example of how to do this?</p> <p>@kevin - thx for link - but I still get error. ("every derived table must have it's own alias")</p> <p>Here's what I did (modified syntax form above article - to 'fit' MySQL) - I clearly missed something...</p> <pre><code>SELECT parents.* FROM ( SELECT taskID, task, parentID, 0 as level FROM tasks WHERE taskidID = 9147 UNION ALL SELECT taskID, task, parentID, Level + 1 FROM tasks WHERE taskID = (SELECT parentID FROM parents ORDER BY level DESC LIMIT 1) ) </code></pre> <p>thoughts???</p> <p>EXAMPLE:</p> <pre><code>ID PARENTID NAME 9146 0 thing1 9147 0 thing2 9148 9146 thing3 9149 9148 thing4 9150 0 thing5 9151 9149 thing6 </code></pre> <p>Query for parents of "thing3" Returns "9148,9146"</p> <p>Query for parents of "thing6" Returns "9149,9148,9146,0"</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload