MySQL nested hierarchy selection

I have the following nested hierarchy in MySQL:

ROOT
|
+--Group1
|
+--Group2
|   |
|   +--SubGroup1
|   |
|   +--ABC
|
+--Group3
|  |
|  +--SubGroup2
|
+--Group4
|  |
|  +--SubGroup1
|     |
|     +--ABC

      

        
        
        
      

    

Table content:

mysql> select * from 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 am trying to select an entire tree from a nested hierarchy in MySQL.

SELECT
    CONCAT( REPEAT(' ', COUNT(parent.group_name) - 1), node.group_name) AS group_name
FROM 
    nest AS node,
    nest AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY
    node.group_name
ORDER BY
    node.lft;

      

        
        
        
      

    

And get results with duplicate children for the first match only:

+----------------+
| group_name     |
+----------------+
| ROOT           |
|  Group1        |
|  Group2        |
|      SubGroup1 |
|        ABC     |
|  Group3        |
|   SubGroup2    |
|  Group4        |
+----------------+

      

        
        
        
      

    

How can I get duplicate results for Group4, similar to Group2? Like this:

+----------------+
| group_name     |
+----------------+
| ROOT           |
|  Group1        |
|  Group2        |
|      SubGroup1 |
|        ABC     |
|  Group3        |
|   SubGroup2    |
|  Group4        |
|      SubGroup1 |
|        ABC     |
+----------------+

      

        
        
        
      

    

Thank.