MySQL recursive query for retrieving all child records in hierarchical data structure

Other Questions MySQL
1

I’m working with a MySQL database that has a hierarchical structure. My table looks like this:

item_id title parent_item_id
15 section1 0
16 section2 15
17 section3 16
18 section4 17

I need to write a single MySQL query that can take a parent ID (like item_id=15) and return all descendant IDs in the hierarchy. In this example, it should return IDs 16, 17, and 18.

The depth of the hierarchy can be different for each branch, so I can’t predict how many levels deep it will go. I could solve this with multiple queries in a loop, but I’m looking for a way to do it with just one MySQL statement. How can I create a recursive query that traverses the entire tree structure?

2

MySQL 8.0+ makes this simpler by using recursive CTEs. You can define a Common Table Expression that begins with your root node and recursively joins to gather all child records. Here’s an example of how to achieve it:

WITH RECURSIVE hierarchy_tree AS (
    SELECT item_id, title, parent_item_id
    FROM your_table_name
    WHERE item_id = 15
    UNION ALL
    SELECT t.item_id, t.title, t.parent_item_id
    FROM your_table_name t
    INNER JOIN hierarchy_tree h ON t.parent_item_id = h.item_id
)
SELECT item_id
FROM hierarchy_tree
WHERE item_id != 15;

This query captures the starting node initially, with the recursive section continuing to find children until none remain. For MySQL versions 5.7 and earlier, consider using a stored procedure or handling it via application code since those versions do not allow recursive CTEs.

3

you can use a recursive CTE in your SQL query. something like: WITH RECURSIVE cte AS (SELECT item_id FROM table WHERE parent_item_id = 15 UNION ALL SELECT t.item_id FROM table t JOIN cte ON t.parent_item_id = cte.item_id) SELECT * FROM cte; this should get all the child records.

4

CTEs work great for this, but watch out if you’ve got really deep hierarchies. MySQL’s recursion limit defaults to 1000 levels, so you might need a stored procedure with a temp table instead. For most cases though, the CTE approach works fine. Just make sure your parent_item_id values don’t create loops - I’ve seen this break things in production when data integrity wasn’t maintained properly. Also, definitely add an index on parent_item_id if you haven’t already. Makes a huge difference for hierarchical queries.