Recursive parent name concatenation in mysql

Amira Elsayed Ismail 0
Viewed 39

I have two tables

Course with the following fields (ID, name, categoryID) Category with the following fields (ID, name, parentCategoryID)

for Example

Category

ID ------- name ------- parentCategoryID

1 -------- Category1 ----- 0

2 -------- Category2 ----- 1

3 -------- Category3 ----- 2

Course

ID ------- name ------- categoryID

1 -------- Course1 ----- 1

2 -------- Course2 ----- 2

3 -------- Course3 ----- 3

I want to write select statement to get the following result

Result

ID ------- name ------- category

1 -------- Course1 ----- Category1

2 -------- Course2 ----- Category2 - Category1

3 -------- Course3 ----- Category3 - Category2 - Category1

which means I want to get all category until root category with ID 0 and concatenate them to get course category name

can anyone help me please ?

EDIT

with recursive
n as (
  select mc.id, mc.fullname , mc.shortname , mcc.name  as category, mcc.parent 
  from mdl_course mc
  join mdl_course_categories mcc on mcc.id = mc.category 
 union all
  select n.id, n.fullname, n.shortname, concat(n.category, ' - ', mcc.name) as category , mcc.parent 
  from n
  join mdl_course_categories mcc on mcc.id  = n.parent
)
select id, fullname, shortname, category from n
hierarchical-data mysql
1 Answers

You can do:

with recursive
n as (
  select r.id, r.name, cast(c.name as char(500)) as category, c.parent_category_id
  from course r
  join category c on c.id = r.category_id
 union all
  select n.id, n.name, concat(trim(category), ' - ', c.name), c.parent_category_id
  from n
  join category c on c.id = n.parent_category_id
)
select id, name, category from n where parent_category_id is null

Result:

 id  name     category                          
 --- -------- --------------------------------- 
 1   Course1  Category1                         
 2   Course2  Category2 - Category1             
 3   Course3  Category3 - Category2 - Category1

See running example at db<>fiddle.

Note: I replaced the values 0 for nulls, since this is the most standard way of representing a lack of parent. You can still use zeroes, but you'll need to check the logic.

The Impaler0
Related