Getting hierarchy data from a self-referencing table

Question

Let's say you have the following table:
items(item_id, item_parent)
... and it is a self-referencing table as item_parent refers to item_id.

What SQL query would you use to SELECT all items in the table along with their depth where the depth of an item is the sum of all parents and grand parents of that item.

If the following is the content of the table:

item_id     item_parent
----------- -----------
1           0          
2           0            
3           2          
4           2          
5           3          

... the query should retrieve the following set of objects:

{"item_id":1,"depth":0}
{"item_id":2,"depth":0}
{"item_id":3,"depth":1}
{"item_id":4,"depth":1}
{"item_id":5,"depth":2}

P.S. I'm looking for a MySQL supported approach.

Answer 1

If the database is SQL 2005 / 2008 then...

The easiest way to get this is using a CTE (Common Table Expression) that is designed to recurse.

 WITH myCTE (Item_id, Depth)
 AS
 (
    Select Item_ID, 0 as Depth From yourTable where Item_Parent=0
    Union ALL
    Select yourTable.Item_ID, Depth + 1 
    From yourTable 
    inner join myCte on yourTable.item_Parent = myCte.Item_Id
 )

 Select Item_id, Depth from myCTE

The output is as follows:

Item_Id  Depth
    1   0
    2   0
    3   1
    4   1
    5   2

From that you can format it as you wish.

Answer 2

MySQL

• http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
• http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

EDIT: removed unnecessary information

Answer 3

Oracle has a very convenient syntax for retrieving hierarchical data like this:

select
    item_id,
    item_parent,
    level as depth
from
    items
connect by
    prior item_id = item_parent
start with
    item_parent not in (select item_id from items)

This starts with the root nodes of your trees as those items whose item_parent does not exist in the table as item_id, and selects all children of those nodes, along with their depth in the tree.

Answer 4

There is a good tech article on the mysql website about hierarchical data in MySql: Managing Hierarchical Data in MySQL - you can find a few detailed solutions with pro and cons there.

Especially the part about "The Nested Set Model" and "Finding the Depth of the Nodes" should be of interest for you.

Answer 5

I need to find a solution for the same task, found some articles, but still didn't choose which way to go...

http://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

May be these links might help you. If you find a good solution - please post it here. i'm not allowed to post more then 1 link - i will add some to next posts