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".

Answer 1

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.