SQL query to retrieve all nodes from X up until the root node in a tree structure
Joined: Jan 10, 2008
In my SQL-database 'exampledb' I've got a table called 'node' created using: (MySQL specific data included)
This table holds id's of nodes as well as the id of their parent node. For example this tree (nodes represented by the id numbers):
Is represented in the node table as
This creates implicit single linked lists from each node through its ancestors up to the root node:
To begin with I need to create an SQL query which takes a node id, and outputs all rows from that node up to and ending with the root node. What would you think is the best way? If it's possible using a single SQL query that would be best. I'm still pretty new to SQL. [ April 25, 2008: Message edited by: Kent Larsson ]
I do know how to make normal SELECT queries. But for this one I would need some kind of recursive call. I don't even know if it's possible using SQL, and Google unfortunately gave me nothing I could use with normal SQL.
I did find that the Oracle specific statement CONNECT BY could be use for this kind of stuff. But I need something using normal SQL, if it's at all possible. [ April 28, 2008: Message edited by: Kent Larsson ]
I would think for true unbounded recursion you would need a stored procedure such as the Oracle specific one you found since stored procedures permit functional programming. I can't quite imagine an unbounded recursive solution being implemented with standard SQL commands, although there might be a trick I'm forgetting.
Kent, It's not possible without using database specific SQL. This article describes what to do for db2 and Oracle. I don't know of a way to do the same in mySql short of a stored proc or a ton of database roundtrips.