• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why don't you show us what you have so far?
 
Kenneth Gustafsson
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
reply
    Bookmark Topic Watch Topic
  • New Topic