File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL query to retrieve all nodes from X up until the root node in a tree structure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query to retrieve all nodes from X up until the root node in a tree structure" Watch "SQL query to retrieve all nodes from X up until the root node in a tree structure" New topic
Author

SQL query to retrieve all nodes from X up until the root node in a tree structure

Kenneth Gustafsson
Ranch Hand

Joined: Jan 10, 2008
Posts: 40
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 ]
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

Why don't you show us what you have so far?


My Blog: Down Home Country Coding with Scott Selikoff
Kenneth Gustafsson
Ranch Hand

Joined: Jan 10, 2008
Posts: 40
I really haven't got anything yet.

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 ]
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL query to retrieve all nodes from X up until the root node in a tree structure