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