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 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
Best Answer
What you are looking is simply not facilitated by MySQL as of writing this answer. Common Table Expressions (CTEs) are supported by several databases. @a_horse_with_no_name posted an answer naming those RDBMSs.
I have posted recursive Stored Procedures: Find highest level of a hierarchical field: with vs without CTEs. However, you emphasized not using any Stored Procedures. To do what you are asking does require Stored Procedures in MySQL.
Compromise Solution...
You will still need a Stored Procedure, but not using recursion. Using a simple loop, you can construct a self-join query and executed it dynamically. Given the depth of the tree you are looking for, here is such a Stored Procedure:
After loading your data into MySQL on my desktop, I ran the Stored Procedure with...
Depth 1
Depth 2
Depth 3 .. 6
Notice that
Depth 6
has no return value. To find the maximum depth, you would have to iterate on it until no rows comes back.CAVEAT
Give it a Try !!!