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)