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

Creating Multithreaded Client-Server Tic Tac Toe using Java Socket programming : Week #1

10 Best Python Courses for Coding Interviews

Using xdotool as a Tiling Window Solution

Observability on K8s — DataDog Autodiscovery and DogStatsD

Why String is Immutable in Java?

Automated MariaDB Replication using Docker

Redis persistence configuration using snapshot

How Does AWS Terminology Translate To Standard Network Terminology: A Quick Guide

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

Stateless-ish Authentication

Oracle: How to Mask Sensitive Data with the Views — Quickly and Free

ACID(SQL) vs BASE(No-SQL) properties

ACID vs BASE properties (SQL vs NoSQL)

Continuous Sub Array Sum (Leet Code)