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;
  1. Output using concat(c.path_info, p.menu_order, p.id)

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to “think OOP” — The game of Classerole

Multifunctional Developers

Robot Framework, A Good Way to Start With Test Automation

How to dual boot Windows 10 and Ubuntu 18.04 on the 15 inch Dell XPS 9570 with Nvidia 1050 ti GPU

Adding bulk users to Teams private channels

Let family and friends be your personal DJ on the road

This is my brother with his “sun protection gear” and the bike on some mountain in the south of Greece in October 2020.

My First PR — Beginning of the Journey

How to create custom template in Azure Devops ?

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
Code Factory

Code Factory

More from Medium

How to Use AWS Parameter Store

Example of Spring Cloud Function with AWS Lambda

Deploy Spring Boot Application to Elastic Beanstalk with Github Actions

Introduction to RabbitMQ