How to write a recursive SQL query

Question

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

have you looked into mysql 8 and recursive cte, if you don't have mysql 8 you need to look for hierachyl tree in mysql — nbk
– nbk, Commented Oct 22, 2021 at 19:48
unfortunately, I am using MySQL 5.7. Hierarchy tree can be used here? — matlab user
– matlab user, Commented Oct 22, 2021 at 19:51
you should switch to mysql 8 but you can start mikehillyer.com/articles/managing-hierarchical-data-in-mysql and then find your way around all obistcles — nbk
– nbk, Commented Oct 22, 2021 at 20:14

nachospiu · Accepted Answer · 2021-10-23 13:10:03Z

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

edited Oct 23, 2021 at 13:10

answered Oct 22, 2021 at 20:41

nachospiu

2,0792 gold badges10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

matlab user Over a year ago

Thanks nachospiu! But why the last tag isn't fetched (2, null, tag2)? I modified my question because I want to display it. Any idea how I can modify this query to have it?

nachospiu Over a year ago

You are welcome @matlabuser! It happens because its parentId is null, I updated my answer and fixed it.

matlab user Over a year ago

Yes exactly. Thank you a lot @nachospiu.

Collectives™ on Stack Overflow

How to write a recursive SQL query

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related