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

If you have a lot of levels, or a lot of data, this will be impractical.

Otherwise, you can try to emulate what the recursive functions do. Even if you won't get the same performance, this won't matter if you do not have much data, nor too many levels.

The general procedure would be as follows:

1. For a given parent, select the record with that ID
2. Also, select records with that as the PARENT_ID, and UNION this with the previous
3. The sub-query in step #2 gives the first-level children, so you can write a query that selects their IDs, and use this as a sub-select in a third query that say "where PARENT_ID IN (sub-select)"
4. This will be analogous to step #3, and go on this way

It will look something like this: (see the Fiddle here)

select *
from prarent P0
where id = 3

union 

select *
from prarent P1
where parent_id = 3

union 

select *
from prarent P2
where parent_id in
 (  select distinct id
    from prarent P1
    where p1.parent_id = 3
 )

union 

select *
from prarent P3
where parent_id in
 (  select distinct id
    from prarent P2
    where parent_id in
    (  select distinct id
       from prarent P1
       where p1.parent_id = 3
    )
 )

Answer 2

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,....