0

Since it looks like recursive queries aren't possible in MySQL, I am wondering if there is a solution to get the same information that also limits the number of queries I make to the database. In my case I have what amounts to a tree and given a node, I make a path back to the root and save the name of the nodes as I go. Given a table like this:

 id | parent
-------------
 1  |   
 2  |   1
 3  |   1
 4  |   2
 5  |   2
 6  |   5

I want to select all ids on the path from 6 back to 1 (6,5,2,1). Since the total length of the path is unknown I would assume that the only way to do this is taking the results from one query and build a new query until I am back at the root. Then again it has been a couple years since I last used MySQL so it wouldn't surprise me if I am a little out of touch. Any help would be appreciated.

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
phill
  • 466
  • 7
  • 17
  • 3
    http://dev.mysql.com/tech-resources/articles/hierarchical-data.html –  Mar 23 '11 at 00:02
  • 3
    [Also check out this comprehensive previous question about storing hierarchical data in a relational database](http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database). – Charles Mar 23 '11 at 00:06

1 Answers1

1

Since it looks like recursive queries aren't possible in mySQL

mySQL doesn't support the 'CONNECT BY' operator, true - but you can implement recursive procedures/functions using mysql and return result sets from them.

symcbean
  • 47,736
  • 6
  • 59
  • 94