Skip to content
Advertisement

Recursive query in tree MySQL8 / MariaDB10

I have a tree-like structure for categories on my database:

How can i build the URL of a category, by concatenating its url with the parent url? (until parent is not null)

I’m using MariaDB 10.2

Advertisement

Answer

This recursive CTE will form a URL out of each level in the table, joining them with a / (although that is easily changed). The desired final node is specified in the CTE (in this example, it is 7):

Demo on dbfiddle

You can get all of the available paths by omitting the WHERE id = condition from the CTE. For my sample data, that gives:

Demo on dbfiddle

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement