CODEWITHSUNDEEP
mysqlsqltree
Smith
Smith

Reputation: 5951

get parent of grand children tree stored in database

I stored a tree in a table. The tree have the structure:

Category > Sub category > sub sub category

The parent here is the one with parent_id of 0. I need to get the cat_id of any category with parent id = 0

cat_id | parent_id | name
-------------------------
1      |    0      | a
2      |    0      | b
3      |    1      | c
4      |    2      | d
5      |    3      | e
6      |    4      | f

so for the cat_id of 6, the top cat_id of whci the parent_id = 0

The only way is to select three sql statement like these

Select parent_id where cat_id  =  6  // the answer is 4
Select parent_id where cat_id  =  4  // the answer is 2
Select parent_id where cat_id  =  2  // the answer is 0

i need to find a more effective way to find the parent without three sql statement

Upvotes: 0

Views: 67

Answers (2)

potatopeelings
potatopeelings

Reputation: 41075

Try this - this will trace up the parents one by one till it gets to the topmost one and it will return that.

select @pv from
(select @pv:=t.parent_id
from (select * from cat order by cat_id desc) t
join (select @pv := 7) tmp
where t.cat_id = @pv) a limit 1;

Replace 7 by 6 or whichever leaf you want. If that leaf doesn't exist, no rows are returned.

Upvotes: 2

Jossonate
Jossonate

Reputation: 7

  1. Your question is not clear enough to understand what do yo want.
  2. You my also want to list out a categories table and the output

Upvotes: -1

Related Questions