Getting hierarchy data from self-referencing tables
Solution 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.
Solution 2
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.
Solution 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.
Solution 4
MySQL
- http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
- http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
EDIT: removed unnecessary information
Related videos on Youtube
Emanuil Rusev
Designer, developer, minimalist, maker of Nota, Historie, Parsedown.
Updated on September 18, 2020Comments
-
Emanuil Rusev over 3 years
Let's say you have the following table:
items(item_id, item_parent)
... and it is a self-referencing table -
item_parent
refers toitem_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.
-
D'Arcy Rittich about 14 yearsWhat database and version? Recursive queries are vendor-specific, if supported at all.
-
Andrew Sledge about 14 years@RBarryYoung: That is assuming that he is using MS SQL Server.
-
João Paladini about 14 yearsTrue, but Recursive CTE's are part of the standard and SQL Server is not the only product that supports them.
-
João Paladini about 14 yearsEmmanuil: If you need MySql specific answers, then you should have specified that somewhere.
-
Emanuil Rusev about 14 years@RBarryYoung: I apologize about that. I wrongly assumed that the answer would apply to any DBMS.
-
-
Emanuil Rusev about 14 yearsThanks for the suggestion! I'd love to see a MySQL supported approach.
-
João Paladini about 14 yearsEmanuil: it is your responsibility to inform folks of implementation requirements (like MySQL) before they try to answer your question.
-
user296355 about 14 years
-
user296355 about 14 years
-
jett about 10 yearsI 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
-
You Help Me Help You about 8 yearsUnfortunately artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html link is dead.
-
murtho over 4 yearsIn case someone is wondering if Doctrine supports this, there is a nice extension: github.com/Atlantic18/DoctrineExtensions/blob/master/doc/…