-1

I have a fairly straightforward self-referenced table like the following:

TABLE 1

id      parent_id
1       NULL
2       1
3       NULL
4       1
5       2
6       5
7       2
8       4

I need to generate a new table where for each element, all their descendants are associated. See this example:

TABLE 2

element  descendants
1        2
1        5
1        6
1        7
1        4
1        8
2        5
2        6
2        7
5        6
4        8

Notice that 3 is not present because it doesn't have any children.

How can I achieve this using a stored procedure? I can get a direct parent-child relation, but I'm having a hard time getting all descendants for a given element.

(real world table is ~15k rows & ~7 level hierarchy, but level it is not predefined so lets assume is N)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Matías Cánepa
  • 5,770
  • 4
  • 57
  • 97

1 Answers1

1

This can be done with a recursive CTE. MySQL now supports recursive CTEs, as described in the MySQL Server Blog.

Assuming a table named "self_ref", the recursive CTE would be something like:

with recursive ref(element, descendant) as (
select parent_id, id from self_ref
union 
select element, id
from ref as r
    inner join self_ref as s on s.parent_id = r.descendant
where parent_id is not null
)
select element, descendant from ref
where element is not null
order by element, descendant;

(This was written for Postgres, but the MySQL syntax is similar if not identical.)

rd_nielsen
  • 2,407
  • 2
  • 11
  • 18
  • Is this new in MySQL 8? I'm using 5.7 – Matías Cánepa Nov 20 '16 at 15:48
  • Yes. If you can't upgrade, considering copying your data to a DBMS that supports recursive CTEs, doing the work there, and then copying the data back to MySQL. A local install of Postgres could serve this purpose for you. – rd_nielsen Nov 20 '16 at 15:55