MySQL: Find top/ultimate parent

Question

I've found a few things similar but nothing quite what I need.

ID  |   PARENT
1   |   NULL
2   |   5
3   |   6
4   |   6
5   |   NULL
6   |   9
7   |   NULL
8   |   7
9   |   8
10  |   NULL

I have a table that looks like the below and need to find the top-most parent using MySQL. The trouble I'm having in this is that there could be up to 20 in the heirarchy steps to get to the top parent and I think I'm just not well versed enough in MySQL to figure it out.

Thanks in advance!

NULL parent means it is the top most parent. Normally this would be done with recursive queries (using common table expressions or connect by prior in oracle) However, my SQL doens't have this. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ offers a method to do it but I've not used it myself. http://stackoverflow.com/questions/3137674/mysql-best-method-to-handle-this-hierarchical-data offers a method as well. — xQbert, Apr 06 '16 at 17:03
So-called edge- or adjacency lists aren't so great when it comes to trees of unlimited or uncertain depth. Options include: joining the table to itself as often as could be required, creating a sproc to handle the recursion, handle the recursion in application level code, or switch to an alternative model (e.g. nested sets) — Strawberry, Apr 06 '16 at 17:06
Possible duplicate of [Hierarchical queries in MySQL](http://stackoverflow.com/questions/8104187/hierarchical-queries-in-mysql) — Naruto, Apr 06 '16 at 17:09

score 2 · Answer 1 · answered Apr 06 '16 at 19:28

Here is an answer from me. Its not nice, but it works. the first query is for MariaDB with the sequence Engine and the longer is fron "normal" MySQL

The start Parameter (id) must set @sid := 8 here

MariaDB

SELECT path, sid
FROM (
  SELECT *, @sid AS sid, @path := CONCAT(@path,' -> ', @sid) AS path,
  (SELECT (@sid:=parent) FROM mytab WHERE id = @sid) AS parent  
  FROM seq_1_to_99
  CROSS JOIN ( SELECT @sid := 8 , @path :=@sid) AS parameter
  HAVING parent = @sid
) AS result
ORDER BY seq DESC
LIMIT 1;

MySQL

SELECT path, sid FROM (
SELECT *, @sid AS sid,
 @path := CONCAT(@path,' -> ', @sid) AS PATH,
 (SELECT (@sid:=parent) FROM mytab WHERE id = @sid) AS parent

FROM (
  SELECT *
  FROM (
    SELECT d2.a*10+d1.a  AS nr
    FROM (
        SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
        SELECT 8   UNION ALL SELECT 9) AS d1
      CROSS JOIN (
        SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  ) AS d2
  ) AS counter
  ORDER BY counter.nr
) AS result
CROSS JOIN ( SELECT @sid := 8 , @path :=@sid) AS parameter
HAVING parent = @sid
) AS p
ORDER BY nr DESC
LIMIT 1;

Samples

MariaDB []> select * from mytab;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  2 |      3 |
|  3 |   NULL |
|  4 |   NULL |
|  5 |      4 |
|  6 |      5 |
|  7 |      8 |
|  8 |      6 |
+----+--------+
8 rows in set (0.00 sec)

MariaDB []> SELECT path, sid
    -> FROM (
    ->   SELECT *, @sid AS sid, @path := CONCAT(@path,' -> ', @sid) AS path,
    ->   (SELECT (@sid:=parent) FROM mytab WHERE id = @sid) AS parent
    ->   FROM seq_1_to_99
    ->   CROSS JOIN ( SELECT @sid := 8 , @path :=@sid) AS parameter
    ->   HAVING parent = @sid
    -> ) AS result
    -> ORDER BY seq DESC
    -> LIMIT 1;
+-------------+------+
| path        | sid  |
+-------------+------+
| 8 -> 6 -> 4 | 4    |
+-------------+------+
1 row in set (0.01 sec)

MariaDB []> SELECT path, sid FROM (
    -> SELECT *, @sid AS sid,
    ->  @path := CONCAT(@path,' -> ', @sid) AS PATH,
    ->  (SELECT (@sid:=parent) FROM mytab WHERE id = @sid) AS parent
    ->
    -> FROM (
    ->   SELECT *
    ->   FROM (
    ->     SELECT d2.a*10+d1.a  AS nr
    ->     FROM (
    ->         SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    ->         SELECT 4   UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
    ->         SELECT 8   UNION ALL SELECT 9) AS d1
    ->       CROSS JOIN (
    ->         SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3  ) AS d2
    ->   ) AS counter
    ->   ORDER BY counter.nr
    -> ) AS result
    -> CROSS JOIN ( SELECT @sid := 8 , @path :=@sid) AS parameter
    -> HAVING parent = @sid
    -> ) AS p
    -> ORDER BY nr DESC
    -> LIMIT 1;
+-------------+------+
| path        | sid  |
+-------------+------+
| 8 -> 6 -> 4 |    4 |
+-------------+------+
1 row in set (0.01 sec)

MariaDB []>

I hope it helps you a little bit.

"A Sequence engine allows the creation of ascending or descending sequences of numbers (positive integers) with a given starting value, ending value and increment." see: https://mariadb.com/kb/en/sequence-storage-engine/ — Bernd Buffen, Oct 07 '20 at 12:28
ok, thanks. probably this doubt requires a separate question. Just want to know it is possible or not. I already have a query containing the @sid or 8. So is it possible to club both queries and return the root value from this query along with the other big select query? just give me an idea. — Jithesh Kt, Oct 07 '20 at 12:45
You can open a new question or send me the query per mail. My email is in my profile — Bernd Buffen, Oct 07 '20 at 12:57

MySQL: Find top/ultimate parent

1 Answers1