I have a table named concept_has_parent_concepts with concept_ids and their parent_ids, the concept_ids are actually the child_ids pointing to their parent.

I want to find all the children of a parent, but the children can also have children and so on.

So the main focus is to find all the children, but also their children of a certain parent with id = 300264091. I want to achieve this with MySQL 8 recursive function, and I came up with this:

WITH RECURSIVE runner (concept_id, parent_id) AS (
    SELECT concept_id AS child_id FROM concept_has_parent_concepts WHERE parent_id = 300264091
    UNION ALL
    SELECT concept_id, parent_id FROM runner WHERE concept_id = child_id
)
SELECT concept_id, parent_id FROM runner

But it doesn't work, it gives an error can't find child_id (SQL Fout (1054): Unknown column 'concept_id' in 'field list'). I'm new to the recursive function, and I'm confused. What approach should I follow to find all the childrens(children) of a parent?

Sample data

Some sample data with more simple ID's, where parentid with NULL conceptid is already top parent:

INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('1',NULL);
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('2',NULL);
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('3','1');
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('4','1');
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('5','2');
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('6','2');
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('7','3');
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('8','7');
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('9','8');
INSERT INTO concept_has_parent_concepts (concept_id,parent_id) VALUES ('10','9');

Expected result

Expected result with finding all childrens children of conceptid = 1, is finding concepts 3,4,7,8,9 and 10. For finding all childrens-children of conceptid = 2 I expect finding concepts 5, 6.

CodePudding user response：

A couple of problems:

• In any UNION query (not only CTEs), all queries must have the same columns. In the case of a CTE, the queries must have the columns as those you define after the CTE name.

• The columns you define after the name of the CTE are the columns of the CTE. Any <column> AS <alias> you define in the CTE body are ignored. You define the columns of the CTE as (concept_id, parent_id), so those are the only names you can reference.

• The latter query inside a recursive CTE is typically a JOIN between the CTE and the base table.

Here's what I tested:

WITH RECURSIVE runner (concept_id, parent_id) AS (
    SELECT concept_id, parent_id FROM concept_has_parent_concepts 
    WHERE concept_id = 1
    UNION ALL
    SELECT c.concept_id, c.parent_id FROM runner AS r 
    JOIN concept_has_parent_concepts AS c ON r.concept_id = c.parent_id
)
SELECT concept_id, parent_id FROM runner;

Output:

 ------------ ----------- 
| concept_id | parent_id |
 ------------ ----------- 
|          1 |      NULL |
|          3 |         1 |
|          4 |         1 |
|          7 |         3 |
|          8 |         7 |
|          9 |         8 |
|         10 |         9 |
 ------------ ----------- 

CodePudding user response：

When dealing with recursion, it's important to define the two steps:

• base step: getting the first child for parents that have no parents
• recursive step: matching cte's child with tab's parent, then keeping cte's parent and tab's child (example: if A->B and B->C and C->D and D->E, we want to keep A->C at step1, A->D at step2, A->E at step3, etc...).

Conditions on the inner join will ensure these steps are correctly executed. A final aggregation with GROUP_CONCAT and partition on "parent_id" will make you see the ancestors and children for each ancestor.

WITH RECURSIVE cte AS (
    SELECT t1.concept_id AS parent_id, 
           t2.concept_id AS child_id
    FROM       tab t1
    INNER JOIN tab t2
            ON t1.concept_id = t2.parent_id
    WHERE t1.parent_id IS NULL
  
    UNION ALL
  
    SELECT cte.parent_id, tab.concept_id
    FROM       cte 
    INNER JOIN tab 
            ON cte.child_id = tab.parent_id
)
SELECT parent_id, GROUP_CONCAT(child_id) AS children
FROM cte
GROUP BY parent_id

Check the demo here.