0

I have a table like this:

child:      parent:
0003        0003
20010       0003
15003       0003
20013       20013
1234        20013
0003        20013

I am looking for a query to select the sub-tree of a given parent including the level of each child, i.e. if the given parent is "20013", the output must be:

child: level:
 [1234, 1]
 [0003, 1]
 [20010, 2]
 [15003, 2]

Important detail I can not use cte query so I would be happy for a solution with a standard query

Attaches the code in NODEJS that brings me the result I need to perform this operation only in the mysql procedure:

async function getCldLvl(DB, item, sons, level = 0, treetype) {
    level++;
    let ele1;
    if (treetype)
        ele1 = await myq(`use ??; SELECT * FROM trees WHERE TreeType=? AND ParentKey=?;`, [DB, treetype, item]);
    else
        ele1 = await myq(`use ??; SELECT * FROM trees WHERE ParentKey=? AND ItemKey!=ParentKey;`, [DB, item]);
    const elements = ele1[1];
    if (elements.length != 0) {
        let p = [];
        for (let index = 0; index < elements.length; index++) {
            const e = elements[index];
            if (e.ItemKey != e.ParentKey) {
                sons.values.push({"ItemKey":e.ItemKey, "Quantity":e.Quantity, "Level":level});
                p.push(getCldLvl(DB, e.ItemKey, sons, level, e.TreeType));
            }
        }
        await Promise.all(p);
    }
}


let sons = { values: [] };
await getCldLvl(DB, "apple", sons);
console.log(sons);

Thanks :)

Good Programmer
  • 23
  • 1
  • 7

1 Answers1

0

You can use a recursive CTE:

with recursive cte as (
      select t.child, 1 as lev
      from t
      where parent = ?
      union all
      select t.child, lev + 1
      from cte join
           t
           on t.parent = cte.child
     )
select *
from cte;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786