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

[Kenneth Gustafsson]

In my SQL-database 'exampledb' I've got a table called 'node' created using: (MySQL specific data included)

CREATE TABLE  `exampledb`.`node` (
  `id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `FK6D20BC9B57485125` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;

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):

    1
   / \
  2   3
       \
        4

Is represented in the node table as

id | parent_id
---|----------
1  | null
2  | 1
3  | 1
4  | 3

This creates implicit single linked lists from each node through its ancestors up to the root node:

A node -> [id | ... | parent_id  --]-------[-- id | ... | parent_id  --]--- ..... ----[-- id | ... | null ] <- 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]

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

[Kenneth Gustafsson]

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]

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]

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.