Get top most parent by nth child id?

Question

Now there is a question we commonly use this technique to maintain the parent child relation i.e we store all the entities in one tables with a parent_id column and all top most parents have 0 in the parent_id column this is a good and normalized technique i agree but there is a disadvantage also , it’s slow and inefficient. This is mainly caused by the recursion like for each parent we have to run the query again and again to make a tree

SELECT id FROM `table` WHERE parent_id=something

I have looked at the solutions some might try to do it with any programming language by running query again and again which makes a loads on server , Some have provided the stored procedure but that also involves the recursion.

So my question is can we do it with one database query for the tree(joins or subqueries)?

• if we know the depth or if we don't know the depth ?

• if it is possible so how can we get the top most parent(i.e parent_id=0) of any child?

• if it is not possible then why this technique is so famous, while it has the flaws or we have another solution for this?

  I have added the sql fiddle but it only has the schema

FIDDLE

Answer 1

I don't know if it's possible with MYSQL, I have been working mainly with SQL Server in my career. In SQL Server, it's possible to do it with only 1 query by using the WITH statement.

This demonstrates how to get all children of an object (id=3) at all levels

With pa as (
     select pa1.*
     From prarent as pa1
     Where id = 3
     union all
     select pa2.*
     from pa join prarent as pa2 on pa.id = pa2.parent_id
  )
select * from pa where pa.id != 3

DEMO

Another example to get all parents of an object (id=7) up to the top most

With pa as (
     select pa1.*
     From prarent as pa1
     Where id = 7
     union all
     select pa2.*
     from pa join prarent as pa2 on pa.parent_id = pa2.id
  )
select * from pa where pa.id != 7

DEMO

Another example to get only the topmost parent

With pa as (
     select pa1.*
     From prarent as pa1
     Where id = 7
     union all
     select pa2.*
     from pa join prarent as pa2 on pa.parent_id = pa2.id
  )
select top 1 * 
from pa 
where pa.id != 7
order by id asc

In this example, I assume that the id is incrementally and i use a simple way (just for demonstration purposes) to get the topmost using order by. You may use another technique depending on your database design.

DEMO

Using this similar technique, you can do more, like getting the bottommost child,....