Recursive query in MySQL | Code Factory

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;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store