Get all parents from a circular reference table in MySQL

Question

I have a table with circular reference for parent-child relation. It will look like the one given in this fiddle.

http://sqlfiddle.com/#!9/2d852c/1/0

I need to find out all the parent names for a given ID. Which query I can use here. I have tried joining the table to itself, but it will fetch only one level in the tree. That is it will fetch the immediate parent only. I need to get all the parents.Like if I search with 'third', I need to get 'second' and 'first'

Answer 1

Try this query. Though, not sure if it'll work for all possible test cases.

SELECT *
FROM table1
WHERE parent > 0 AND parent <= (SELECT parent FROM table1 WHERE name LIKE fifth')
ORDER BY id DESC