Recursive query in MySQL | Code Factory


Donate : Link

Medium Blog : Link

Applications : 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)


Advertisement

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s