CODEWITHSUNDEEP
mysqlsqlhierarchical-data
Rahul Tailwal
Rahul Tailwal

Reputation: 3213

mysql query to get the root parent

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.

Upvotes: 2

Views: 1888

Answers (1)

Meherzad
Meherzad

Reputation: 8553

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

Upvotes: 3

Related Questions