Getting hierarchy data from self-referencing tables

Question

Let's say you have the following table:

items(item_id, item_parent)

... and it is a self-referencing table - 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.

What database and version? Recursive queries are vendor-specific, if supported at all. — D'Arcy Rittich
– D'Arcy Rittich, Commented Feb 4, 2010 at 13:40
@RBarryYoung: That is assuming that he is using MS SQL Server. — Andrew Sledge
– Andrew Sledge, Commented Feb 4, 2010 at 13:41
True, but Recursive CTE's are part of the standard and SQL Server is not the only product that supports them. — RBarryYoung
– RBarryYoung, Commented Feb 4, 2010 at 14:14
Emmanuil: If you need MySql specific answers, then you should have specified that somewhere. — RBarryYoung
– RBarryYoung, Commented Feb 4, 2010 at 14:15
@RBarryYoung: I apologize about that. I wrongly assumed that the answer would apply to any DBMS. — Emanuil Rusev
– Emanuil Rusev, Commented Feb 4, 2010 at 14:21

Andrew · Accepted Answer · 2010-02-04 13:47:55Z

24

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.

answered Feb 4, 2010 at 13:47

Andrew

27.4k5 gold badges67 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Emanuil Rusev Over a year ago

Thanks for the suggestion! I'd love to see a MySQL supported approach.

RBarryYoung Over a year ago

Emanuil: it is your responsibility to inform folks of implementation requirements (like MySQL) before they try to answer your question.

Aaron · Accepted Answer · 2010-02-04 13:59:31Z

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.

answered Feb 4, 2010 at 13:59

Aaron

7,1484 gold badges34 silver badges48 bronze badges

1 Comment

jett Over a year ago

I did not know that Oracle had this. This ia good to know. Wouldn't it be more efficient if parents had a null value in the item_parent column so that we can avoid the "not in" and an extra select

Collectives™ on Stack Overflow

Getting hierarchy data from self-referencing tables

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related