1

I have a table like this

id ,  parent_id  , name      , leaf
1  ,  0          ,parent 1   , 0
2  ,  0          ,parent 2   , 0
3  ,  1          ,child  1   , 0
4  ,  3          ,child  2   , 1
5  ,  2          ,child  3   , 0
6  ,  5          ,child  4   , 1
7  ,  0          ,child  5   , 1

And i want select leaves and it`s root name not parent name Something like below 

id  , name     , root_name 
4   ,  child 2  , parent 1
6   ,  child 4  , parent 2
7   ,  child 5  , null

Three may have many levels How can i do it just in MySQL with Stored Procedure ?

Jamaseb Morteza
  • 49
  • 6
  • You will have observed that MySQL presently has no support for recursion. Choices include writing a sproc, handling the logic in application code, joining the table to itself as often as could possibly be required, or switching to an alternative model, e.g. nested sets. All of these topics are discussed widely elsewhere, so I won't elaborate on them here. – Strawberry May 10 '17 at 09:57

2 Answers2

1

You can do something like below to get required output

select t.id, t.name, m.root_name from (select id,parent_id,name,leaf from urtable where leaf=1) t 
join urtable m on (t.parent_id=m.parent_id and t.id=m.id)
Rams
  • 2,129
  • 1
  • 12
  • 19
0

IMO you should have two tables for this. One for Nodes and one for the nodes relation. Then you could do a join and get the results you want.

Read about Database normalization and why it matters, you could normalize your DB until the 3rd normal form.

nodes (
    id,
    name
)

node_to_parent (
    id
    node_id,
    parent_id
)

Then you can query like this:

Select n.name, np.parent_id from nodes
join node_to_parent np 
on np.node_id = n.node_id;
lloiacono
  • 4,714
  • 2
  • 30
  • 46