SQL recursive query (MySQL 5.7)

Question

I don't know how to do an SQL recursive query. The database structure is like this:

Name               Id  Id Parent
Food                0   -1 
Pizza               1    0 
Pasta               2    0 
Pasta with Tomato   3    2

Every Row has a name, an ID (which is the primary key in the table) and a parent id, that is recursive as you can see. If a row doesn't have a parent, then the id is set to -1. In the case of pasta with tomato, I need to take the parent name and, if the parent has a parent itself, I need to take also that name and so on until I reach the root. What is the right recursive query to apply? Thanks to those that will help me!

I'm using MySQL, and unfortunately I don't have either MySQL v8 or the possibility to update it — Lorenzo
– Lorenzo, Commented Dec 13, 2018 at 14:58
Follow the above link, and you can perhaps get some useful tips. — jarlh
– jarlh, Commented Dec 13, 2018 at 14:59

id'7238 · Accepted Answer · 2021-11-05 04:48:34Z

3

I actually had to do LEFT JOINs to expand the table to the desired layout:

select a.*, b.name as parent_name, b.ID as parent_id, c.ID_Parent as parent_ID2, 
       d.name as parent_name2
from [sample_tbl] a
left join [sample_tbl] b
on a.ID_parent = b.ID
left join [sample_tbl] c
on b.ID = c.ID
left join [sample_tbl] d
on c.ID_Parent = d.ID
;

Output:

Name	ID	ID_Parent	parent_name	parent_id	parent_ID2	parent_name2
Food	0	-1	NULL	NULL	NULL	NULL
Pizza	1	0	Food	0	-1	NULL
Pasta	2	0	Food	0	-1	NULL
Pasta with Tomato	3	2	Pasta	2	0	Food

edited Nov 5, 2021 at 4:48

id'7238

2,7261 gold badge6 silver badges12 bronze badges

answered Dec 13, 2018 at 15:38

Wendy Zhu

985 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nico Haase Over a year ago

Are you sure this works properly? What if the tree of parents and children nests over even more levels? And how does this stuff with the brackets work in MySQL?

Collectives™ on Stack Overflow

SQL recursive query (MySQL 5.7)

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related