How to get all children records in MYSQL hierarchical data

Question

I have a table with the following columns

id   |    parent_customer_id
-----------------------------
1    |    0
2    |    0
3    |    1
4    |    2
5    |    4
6    |    4

I'd like a script that can return all the child ids of a certain customer. For example

get_child_ids (1) = 1,3
get_child_ids(2) = 2,4,5,6
get_child_ids(3) = 3
get_child_ids(4) = 4,5,6
get_child_ids(5) = 5
get_child_ids(6) = 6

Some ids could go up to 10 levels deep. I found a great solution to getting parent ids at https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/ but I'm having trouble getting the children

What trouble? Data for id is different then other id in the sample data you shared. — Chetan, Jun 18 '17 at 01:10

Bill Karwin · Answer 1 · 2022-10-21T23:29:48.110

The best solution is "write a recursive SQL query with CTE syntax" but this is not supported until MySQL 8.0.1.

Recursive CTE syntax is standard SQL, supported by all popular brands of SQL-compliant products now that MySQL is supporting it.

I did a presentation about the upcoming recursive query feature in MySQL at the Percona Live Conference in April 2017: Recursive Query Throwdown in MySQL 8.

WITH RECURSIVE MyCTE AS (
  SELECT id, parent_customer_id FROM MyTable WHERE id = ?
  UNION
  SELECT id, parent_customer_id FROM MyTable JOIN MyCTE 
    ON MyTable.parent_customer_id = MyCTE.id
)
SELECT * FROM MyCTE;

If you can't use MySQL 8.0.1 or later, you can use the clever solution at ExplainExtended, or you can store your hierarchical data in another way to support non-recursive queries.

I show a number of solutions in my presentation Models for hierarchical data, or in my answer to What is the most efficient/elegant way to parse a flat table into a tree?.

I also wrote a chapter about this in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

score 0 · Answer 2 · answered Jul 13 '17 at 14:39

You must check following article about Managing Hierarchical Data in MySQL

It's an excellent article that shows you a great technique on how to deal with hierarquical data with "infinite" depth.

Word of Caution: If you're dealing with hierarquical data where child has an unique parent, this is a great solution for you. But if you're dealing with children that have more than one parent, then you're dealing with graphs and for that reason, MySQL it's not suitable for you. You must explore solutions as Neo4J instead.

How to get all children records in MYSQL hierarchical data

2 Answers2