File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Kenneth Gustafsson
Ranch Hand
Posts: 40
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3888
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why don't you show us what you have so far?
 
Kenneth Gustafsson
Ranch Hand
Posts: 40
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3888
15
Eclipse IDE Flex Google Web Toolkit
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 33671
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic