sorting - MySQL Order By Hierarchy -

i asked question previously two-layer tree , solution given worked perfectly.

i have multi level tree (up 3, lets assume there more in future.

my code looks this:

select * fin_document finl  left outer join fin_document finl2 on finl2.id = finl.parent_line_id  order case when finl2.ordinal null finl.ordinal else concat(finl2.ordinal,'-',finl.ordinal) end 

lets assume similar tree before:

(id)  (item)    (#)  (parent_line_id) 1234 - car    -  1 -  null 0000 - boat   -  2 -  null 2222 - house  -  4 -  null 6545 - bike   -  5 -  null 6547 - wheels -  0 -  1234 4442 - bed    -  1 -  2222 1474 - sink   -  0 -  2222 9456 - tires  -  0 -  6547                  *new item, child of wheels 8975 - l.nuts -  1 -  6547                  *new item, child of wheels 

oh , # column "ordinal"

so how sort proper more 1 parent?

the proper sort should like:

(id)  (item)    (#)  (parent_line_id) 1234 - car    -  1 -  null 6547 - wheels -  0 -  1234 9456 - tires  -  0 -  6547 8975 - l.nuts -  1 -  6547  0000 - boat   -  2 -  null 2222 - house  -  4 -  null 1474 - sink   -  0 -  2222 4442 - bed    -  1 -  2222 6545 - bike   -  5 -  null 

note: cannot alter tables whatsoever. able pull data tables, tables managed company, who's software use. i'm aware more , more complex if there more children, not think there more 3-4 children company using for. unfortunately, due complexity, why had return here , ask again :(

hopefully aren't looking going work n deep hierarchy without modification.

this should trivial extend, however.

select id,     item,     o,     parent_line_id (     select *,         1 parentage,         o rank     table1     parent_line_id null      union      select t2.id,         t1.item,         t1.o,         t1.parent_line_id,         2 parentage,         t2.o rank     table1 t1     inner join table1 t2 on t1.parent_line_id = t2.id         , t2.parent_line_id null      union      select t3.id,         t1.item,         t1.o,         t1.parent_line_id,         3 parentage,         t3.o rank     table1 t1     inner join table1 t2 on t1.parent_line_id = t2.id         , t2.parent_line_id not null     inner join table1 t3 on t2.parent_line_id = t3.id     ) q order rank asc,     parentage asc,     o asc; 

demo here

the basic premise identify parentless items, , give them parentage of 1.

we identify children, give them parentage of 2, , children parentage of 3.

all of them inherit first parents ordinal sorting purposes, then.

there other ways this, i'm going them, in mean time - works.