MySQL: Get Root Node of Parent-Child Structure

Question

I have a table similar to this:

=================
| Id | ParentId |
=================
| 1  | 0        |
-----+-----------
| 2  | 1        |
-----+-----------
| 3  | 0        |
-----+-----------
| 4  | 3        |
-----+-----------
| 5  | 3        |
-----+-----------
| 6  | 0        |
-----+-----------
| 7  | 6        |
-----+-----------
| 8  | 7        |
-----------------

Given an Id, I need to know its root "node" Id. So,

• Given 1, return 1
• Given 2, return 1
• Given 3, return 3
• Given 4, return 3
• Given 5, return 3
• Given 6, return 6
• Given 7, return 6
• Given 8, return 7

There is no limit to the levels of the hierarchy. Is there a SQL that can do what I need?

Answer 1

This is quite difficult to do in MySQL because it doesn't yet support recursive common table expressions.

I'd suggest instead using a nested sets model, or else storing the root node in the row and updating it as the structure changes.

Answer 2

In short: no. Look at regular Bill Karwin's excellent presentation about hierarchical models and it's uses, shortcomings, and how to get around those: http://www.slideshare.net/billkarwin/models-for-hierarchical-data