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 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
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
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
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.
Using this similar technique, you can do more, like getting the bottommost child,....