mysql query to get the root parent

Question

I have the following data in table categories:

 id  parent_id    title
 -----------------|-------------------
 1         0      |A
 2         1      |B
 3         2      |C
 4         3      |D
 5         4      |E

Now I want to get the root category title(parent_id 0) for category id 4 and that is category id 1 of title A.

How can I achieve the required result in a single mysql query? I assume procedural query will be used but I dont know how to do it.

I searched around a bit and came across a solution for a problem that is very similar to yours. While I - unfortunately- cannot provide an answer, maybe this helps you: http://stackoverflow.com/questions/2441821/getting-all-parent-rows-in-one-sql-query — sekky, Sep 19 '13 at 11:32
yes that works fine. i have put limit 1 to just get the top category. — Rahul Tailwal, Sep 19 '13 at 11:44

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

Try this query

It works on the assumption that the id of parent are less than child as the records are being sorted in descending as a derived table before actual query.

select 
  @parent:=parent_id as prnt, title, id
from
  (select @parent:=8 ) a
join 
  (select * from tbl order by id desc) b
where 
  @parent=id

Fiddle

| PRNT | TITLE | ID |
|------|-------|----|
|    7 |     q |  8 |
|    6 |     a |  7 |
|    0 |     d |  6 |

Note Best way is to do it is using a stored proc..

mysql query to get the root parent

1 Answers1

Fiddle

Linked