mysql - Get top most parent by nth child id? -

now there question commonly use technique maintain parent child relation i.e store entities in 1 tables parent_id column , top parents have 0 in parent_id column , normalized technique agree there disadvantage , it’s slow , inefficient. caused recursion each parent have run query again , again make tree

select id `table` parent_id=something 

i have looked @ solutions might try programming language running query again , again makes loads on server , have provided stored procedure involves recursion.

so question can 1 database query tree(joins or subqueries)?

• if know depth or if don't know depth ?
• if possible how can top parent(i.e parent_id=0) of child?

• if not possible why technique famous, while has flaws or have solution this?

  i have added sql fiddle has schema

fiddle

i don't know if it's possible mysql, have been working sql server in career. in sql server, it's possible 1 query using with statement.

this demonstrates how children of object (id=3) @ levels

with pa (      select pa1.*      prarent pa1      id = 3      union      select pa2.*      pa join prarent pa2 on pa.id = pa2.parent_id   ) select * pa pa.id != 3 

demo

another example parents of object (id=7) top most

with pa (      select pa1.*      prarent pa1      id = 7      union      select pa2.*      pa join prarent pa2 on pa.parent_id = pa2.id   ) select * pa pa.id != 7 

demo

another example topmost parent

with pa (      select pa1.*      prarent pa1      id = 7      union      select pa2.*      pa join prarent pa2 on pa.parent_id = pa2.id   ) select top 1 *  pa  pa.id != 7 order id asc 

in example, assume id incrementally , use simple way (just demonstration purposes) topmost using order by. may use technique depending on database design.

demo

using similar technique, can more, getting bottommost child,....