How to write a recursive SQL query-CodePudding

I have the following database table: Tag (id, parentId, name). I want to start with one id (my seed id), and fetch all parents of this tag and all its predecessors, until reaches NULL parent.

| id | parentId | name |                 | id | parentId | name |
| -- | -------- | ---- |                 | -- | -------- | ---- | 
| 1  | null     | tag1 |                 | 9  | 8        | tag9 |
| 2  | null     | tag2 |     ==>>        | 8  | 7        | tag8 |
| 3  | 1        | tag3 |                 | 7  | 4        | tag7 |
| 4  | 2        | tag4 |                 | 4  | 2        | tag4 |
| 5  | 4        | tag5 |                 | 2  | null     | tag2 |
| 6  | 4        | tag6 |
| 7  | 4        | tag7 |
| 8  | 7        | tag8 |
| 9  | 8        | tag9 |

I wrote this SQL and it only fetches one row. Any idea what I am doing wrong?

from    (select * from tag order by parentId, id) tags_sorted, (select @var := '9') seed
where   find_in_set(id, @var)
and     length(@var := concat(@var, ',', parentId));

PS: I tried to revert the procedure, and start with parent ID and then fetch all successors, and the same SQL query worked perfectly fine:

from    (select * from tag order by parentId, id DESC) tags_sorted, (select @var := '2') seed
where   find_in_set(parentId, @var)
and     length(@var := concat(@var, ',', id));

CodePudding user response：

You need to sort by parentId DESC (by default sort is ASC):

SELECT *
FROM  (SELECT * FROM tag ORDER BY parentId DESC, id) tags_sorted, (SELECT @var := '9') AS seed
WHERE find_in_set(id, @var) AND length(@var := concat(@var, ',', parentId));

You are looking up the tree, therefore the order is the reverse of the query looking down.

Updated

I think this way you can get the root node (node which parentId is null):

SELECT *
FROM  (SELECT * FROM tag ORDER BY parentId DESC, id ASC) tags_sorted, (SELECT @var := '9') AS seed
WHERE find_in_set(id, @var) AND length(@var := concat(@var, ',', coalesce(parentId, 0)));