MySQL Get path to root

Question

I have a table like

id      | parent_id
________|_________
'A'     | NULL
'B      | 'A'
'C'     | 'B'
'K'     | NULL
'J'     | 'K'
'Y'     | 'J'

I want to query MySQL in such a way that I will have the whole parent path for each id.

like

id      | parent_ids
________|_________
'A'     | NULL
'B      | 'A'
'C'     | 'A','B'
'K'     | NULL
'J'     | 'K'
'Y'     | 'J','K'

Consider upgrading to MySQL 8.0.. MySQL 8.0 supports recursive cte (WITH AS clause) with makes the query more eazy.. Otherwise take a look into this https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — Raymond Nijland, Apr 26 '18 at 13:25
did you take a look into the link i've provided in mine last comment? — Raymond Nijland, Apr 26 '18 at 13:41
It doesn't answer the question..It looks hierarchly for a specific id, I need for everything — aclowkay, Apr 26 '18 at 13:46

score 0 · Answer 1 · answered Apr 27 '18 at 04:48

Unfortunately, MySQL 5 haven't recursive queries.

But if you don't have a lot of levels (for example 4) you can use several queries which is joined with UNION ALL. I think it'll be faster and easy to understand than one dificult and hard query.

Test data

create table products(
  id varchar(1),
  parent_id varchar(1)
);

insert products(id,parent_id)values
('A',NULL),
('B','A'),
('C','B'),
('K',NULL),
('J','K'),
('Y','J');

Demo query

SELECT l1.id,l1.parent_id,NULL parent_ids
FROM products l1
WHERE l1.parent_id IS NULL

UNION ALL

SELECT l2.id,l2.parent_id,l1.id
FROM products l1
JOIN products l2 ON l2.parent_id=l1.id
WHERE l1.parent_id IS NULL

UNION ALL

SELECT l3.id,l3.parent_id,concat(l1.id,',',l2.id)
FROM products l1
JOIN products l2 ON l2.parent_id=l1.id
JOIN products l3 ON l3.parent_id=l2.id
WHERE l1.parent_id IS NULL

UNION ALL

SELECT l3.id,l3.parent_id,concat(l1.id,',',l2.id,',',l3.id)
FROM products l1
JOIN products l2 ON l2.parent_id=l1.id
JOIN products l3 ON l3.parent_id=l2.id
JOIN products l4 ON l4.parent_id=l3.id
WHERE l1.parent_id IS NULL

SQL Fiddle - http://sqlfiddle.com/#!9/0b76f4/16

MySQL Get path to root

1 Answers1