mysql recursive query from node of interest to root node

Question

I have a mysql table as follows:

id | name        | parent_id
19 | category1   | NULL
20 | category2   | 19
21 | category3   | 20
22 | category4   | 21

How do i write a recursive mysql query to fetch all the nodes from input node to root node. E.g: If the input is category3, the output should be category3, category2 and category1

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2019-11-25T05:26:13

We can try using the following recursive hierarchical query:

WITH RECURSIVE cte (id, name, parent_id) AS (
    SELECT     id,
               name,
               parent_id
    FROM       yourTable
    WHERE      parent_id IS NULL
    UNION ALL
    SELECT     p.id,
               p.name,
               p.parent_id
    FROM       yourTable p
    INNER JOIN cte
            ON p.parent_id = cte.id
    WHERE      cte.name <> 'category3'
)

SELECT GROUP_CONCAT(name ORDER BY id DESC) AS names FROM cte;

This outputs:

category3,category2,category1

Demo

The idea here is to short circuit the recursions to stop at the point where the parent would be category3. If the recursive portion of the CTE hits this point, it means that it already one step below the category3 level, and so should stop.

mysql recursive query from node of interest to root node

1 Answers

Demo