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!

Answer 1

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