Getting root parent

Question

+--------+---------+-----------+
|   id   | title   | parent_id |
+--------+---------+-----------+
|    1   | Lvl-1   |   null    |
+--------+---------+-----------+
|    2   | Lvl-2   |   null    |
+--------+---------+-----------+
|    3   | Lvl-11  |     1     |
+--------+---------+-----------+
|    4   | Lvl-12  |     1     |
+--------+---------+-----------+
|    5   | Lvl-121 |     4     |
+--------+---------+-----------+

How do i actualy get root parent for each row
For example, row with id 5 have parent with id 4 and id 4 have parent with id 1, so root id for id 5 is id 1
I dont have any idea on how to do it and is there a way to solve this by using only 1 query

score 3 · Answer 1 · answered Jun 19 '15 at 19:54

Here is a short query doing what you're asking, assuming your table is called foo and that you want to know the root of <id>:

SELECT f.id, f.title
FROM (
    SELECT @id AS _id, (SELECT @id := parent_id FROM foo WHERE id = _id)
    FROM (SELECT @id := <id>) tmp1
    JOIN foo ON @id IS NOT NULL
    ) tmp2
JOIN foo f ON tmp2._id = f.id
WHERE f.parent_id IS NULL

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

2

You are simply not going to believe this

I wrote a post in the DBA StackExchange (October 24, 2011) on how to pull this off using Stored Procedure programming. I also included some sample data and the results.

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 12 '11 at 20:03

RolandoMySQLDBA

43,883
16
91
132

As per his question : "I dont have any idea on how to do it and is there a way to solve this by using only 1 query" as opposed the the procedural loop/concatonate you have here. Solution still works, I'd be curious if there was a 'one select statement' solution – Twelfth Dec 12 '11 at 20:12
SQL Server has the power to perform recursive queries with a single statement. MySQL does not. That's why it is write as proceduual. – RolandoMySQLDBA Dec 12 '11 at 20:14
@RolandoMySQLDBA so basically stored procedure is the only way? i bet this can be done with trigger too.But is it achiveable wit pure query only? – slier Dec 12 '11 at 20:54
No pure SQL way is possible without making some assumptions. For example, if you knew the maximum length of all paths for any id was 7, you could probably write an elaborate LEFT JOIN query of the table against itself 6 times. Once you get the right SQL for ids whose length was less than or equal to 7, what if an id now had a length of 8 or more? The query would have to be dynamically altered to accommodate longer hierarchies. Again, the idea is making that assumption in the design of the query. The stored procedure makes no assumptions. It simply performs preorder tree traversal. – RolandoMySQLDBA Dec 12 '11 at 21:28
@RolandoMySQLDBA haiz, thats why mysql need to implement some sort recursive fucntion like other rdbms do – slier Dec 13 '11 at 07:57

score 0 · Answer 3 · answered Dec 12 '11 at 20:11

0

If your tree structure is more than say two layers deep you're searching for modified preorder tree traversal

answered Dec 12 '11 at 20:11

tback

11,138
7
47
71

modified preorder tree traversal is hard to maintain – slier Dec 12 '11 at 20:49
a stored procedure is expensive to query. it depends on your use case what makes the most sense. – tback Dec 12 '11 at 21:08

Getting root parent

3 Answers3

You are simply not going to believe this

Linked