MySQL nested hierarchy select -

MySQL nested hierarchy select -

i have next nested hierarchy in mysql:

root | +--group1 | +--group2 | | | +--subgroup1 | | | +--abc | +--group3 | | | +--subgroup2 | +--group4 | | | +--subgroup1 | | | +--abc

the table contents is:

class="lang-html prettyprint-override">mysql> select * nest; +----+------+------------+------+ | id | lft | group_name | rgt | +----+------+------------+------+ | 1 | 1 | root | 20 | | 2 | 2 | group1 | 3 | | 3 | 4 | group2 | 9 | | 4 | 5 | subgroup1 | 8 | | 5 | 6 | abc | 7 | | 6 | 10 | group3 | 13 | | 7 | 11 | subgroup2 | 12 | | 8 | 14 | group4 | 19 | | 9 | 15 | subgroup1 | 18 | | 10 | 16 | abc | 17 | +----+------+------------+------+

i trying select tree nested hierarchy @ mysql.

select concat( repeat(' ', count(parent.group_name) - 1), node.group_name) group_name nest node, nest parent node.lft between parent.lft , parent.rgt grouping node.group_name order node.lft;

and results, repeated childs first match:

class="lang-html prettyprint-override">+----------------+ | group_name | +----------------+ | root | | group1 | | group2 | | subgroup1 | | abc | | group3 | | subgroup2 | | group4 | +----------------+

how can repeated results group4, group2? this:

class="lang-html prettyprint-override">+----------------+ | group_name | +----------------+ | root | | group1 | | group2 | | subgroup1 | | abc | | group3 | | subgroup2 | | group4 | | subgroup1 | | abc | +----------------+

thank you.

change

group node.group_name

to

group node.id

like this

select concat( repeat(' ', count(parent.group_name) - 1), node.group_name) group_name nest node, nest parent node.lft between parent.lft , parent.rgt grouping node.id order node.lft

mysql nested hierarchy