Fetch all parents from child id

Question

I created a table with name "vegs" and inserted values just like this.

    id  name            parent
--------------------------------------
    1   fruits          0
    2   Apple           1
    3   Orange          1
    4   Grape           1
    5   Green Apple     2
    6   Red Apple       2

I want to recursively fetch all parents of the child "6".

This might be helpful. Found it on SO. https://stackoverflow.com/questions/12948009/finding-all-parents-in-mysql-table-with-single-query-recursive-query — Divya Gopal, May 07 '19 at 12:01

RolandoMySQLDBA · Answer 1 · 2018-05-31T14:08:33.107

Recursive queries cannot be done with pure SQL when it comes to MySQL.

I have written posts on how to use Stored Procedures to accomplish this

Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
Dec 10, 2012 : MySQL: Tree-Hierarchical query
Jul 15, 2013 : Get top most parent by nth child id?

Give them a Try !!!

NOTE : This post, originally May 24, 2014, predates MySQL 8.0, which now has CTEs.

Fetch all parents from child id

1 Answers1