mysql> describe a;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| parent_id | int(11)     | YES  | MUL | NULL    |                |
| name      | varchar(45) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)

mysql> select * from a;
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
|  1 |      NULL | ROOT |
|  2 |         1 | L1_0 |
|  3 |         1 | L1_1 |
|  4 |         2 | L2_0 |
|  5 |         4 | L3_0 |
|  6 |         4 | L3_1 |
|  7 |         3 | L2_1 |
+----+-----------+------+
7 rows in set (0.00 sec)

mysql> set @t=5; select distinct b.node_id, a.* from a, (select @t:=if(a.id = @t, a.parent_id, @t) as node_id from a order by a.id DESC) as b where a.id=b.node_id; # ищем путь от ноды с индексом 5 до корня, при условии, что дочерний элемент имеет индекс выше, чем свои родитель
Query OK, 0 rows affected (0.00 sec)

+---------+----+-----------+------+
| node_id | id | parent_id | name |
+---------+----+-----------+------+
|       5 |  5 |         4 | L3_0 |
|       4 |  4 |         2 | L2_0 |
|       2 |  2 |         1 | L1_0 |
|       1 |  1 |      NULL | ROOT |
+---------+----+-----------+------+
4 rows in set (0.02 sec)

mysql> truncate a;
Query OK, 0 rows affected (0.19 sec)

mysql> insert into a (id, parent_id, name) values (10, NULL, 'ROOT'), (2, 10, 'L1_0'), (7, 10, 'L1_1'), (8, 7, 'L2_0'), (9, 7, 'L2_1'), (6, 9, 'L3_0'), (4, 9, 'L3_1'), (1,6, 'L4_0'), (3,1,'L5_0');
Query OK, 9 rows affected (0.13 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> select* from a;
+----+-----------+------+
| id | parent_id | name |
+----+-----------+------+
|  1 |         6 | L4_0 |
|  2 |        10 | L1_0 |
|  3 |         1 | L5_0 |
|  4 |         9 | L3_1 |
|  6 |         9 | L3_0 |
|  7 |        10 | L1_1 |
|  8 |         7 | L2_0 |
|  9 |         7 | L2_1 |
| 10 |      NULL | ROOT |
+----+-----------+------+
9 rows in set (0.00 sec)

mysql> set @t=1; select distinct b.node_id, a.* from a, (select @t:=if(a.id = @t, a.parent_id, @t) as node_id from a order by a.id DESC) as b where a.id=b.node_id; # начиная со второй ноды на пути к корню, условие не соблюдено. поэтому получаем только 2 ноды - осходную и ее родителя.
Query OK, 0 rows affected (0.00 sec)

+---------+----+-----------+------+
| node_id | id | parent_id | name |
+---------+----+-----------+------+
|       1 |  1 |         6 | L4_0 |
|       6 |  6 |         9 | L3_0 |
+---------+----+-----------+------+
2 rows in set (0.00 sec)