Recursive query in MySQL | Code Factory

Reference Link : Link

Donate : Link

CREATE TABLE `menu_master` (
    `id` int(11) NOT NULL,
    `menu_name` varchar(45) NOT NULL,
    `menu_action` varchar(45) NOT NULL,
    `parent_id` int(11) NOT NULL,
    `menu_order` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
);
INSERT INTO `menu_master` VALUES 
    (1,'Menu','/menu',0,1),
    (2,'Menu1','/menu1',1,2),
    (3,'Master','/master',0,2),
    (4,'Menu2','/menu2',1,1),
    (5,'Menu1.1','/menu11',2,1),
    (6,'Menu1.2','/menu12',2,2),
    (7,'Menu1.4','/menu14',2,3),
    (8,'Menu1.3','/menu13',2,4),
    (9,'Menu2.1','/menu21',4,1),
    (10,'Menu1.1.1','/menu111',5,1);
with recursive rec_tree as (
    select id,
        CONCAT(CAST('' AS CHAR(20)), menu_name) as menu_name,
        menu_action,
        parent_id,
        menu_order,
        1 as level,
        CAST('' AS CHAR(20)) AS dash_line,
        concat(mm.id) as path_info
    from menu_master mm
    where parent_id = 0
    union all
    select p.id,
        concat(c.dash_line, '--', p.menu_name),
        p.menu_action,
        p.parent_id,
        p.menu_order,
        c.level + 1,
        concat(c.dash_line, '--'),
        #concat(c.path_info, p.menu_order, p.id)
        concat(c.path_info, p.menu_order)
    from menu_master p
    cross join rec_tree c on p.parent_id = c.id
)
select * from rec_tree order by path_info;

1. Output using concat(c.path_info, p.menu_order, p.id)

2. Output using concat(c.path_info, p.menu_order)