How to write a recursive SQL query

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));

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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)));

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0