I think you might consider refactoring your code to use a hybrid of Nested Sets and Adjacency List.
It's fairly trivial to add a 'parent_id' to the nested set, so you really don't loose any of your existing schema structure, but it will be much faster to traverse a tree, and no recursion is required... http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Do a google search for "mysql nested set" and you will find more. Here are some recommend: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://www.sitepoint.com/article/hierarchical-data-database http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=tutorial-ferrara2 &id=3453&open=1&anc=0&view=1 http://simon.incutio.com/archive/2003/06/19/storingTrees http://istherelifeafter.com/joecelko.html http://www.codeproject.com/cs/database/Trees_in_SQL_databases.asp http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html "You had me at EHLO" --E.Webb (10.04.05) > -----Original Message----- > From: Gokhan Demir [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 18, 2006 12:11 PM > To: mysql@lists.mysql.com > Subject: RE: Recursive query > > > After deciding a max level, you can self join the table max > level time. > > Also, you can add two columns, one int column as a hashcode, > and one int > column as showing current level. And by writing a simple > program, you can > update the hashcode field. You can think bitwise. Just > reserve n bits for > each level. Increment the level values by one as you traverse > the tree. That > way you can algo gain the flexibility to select one branch. > (Calculate min > and max hashcode values and select from table where hashcode > column between > the min and max calculated values. > > HTH, > Gokhan > > > -----Original Message----- > From: Peter Lauri [mailto:[EMAIL PROTECTED] > Sent: Thursday, May 18, 2006 8:20 PM > To: mysql@lists.mysql.com > Subject: Recursive query > > Hi, > > This is an example of an table that I store categories in. > Each category > have a id and also a parent. If the parent_id is 0 that category is a > super-category. > > Assume that I would like to get a list of all categories and > its parent > category, grandparents category etc down to super-category. > For example, if > I take category id 13 as an example: > > 13 should be chosen; because that is the one I am working with > 9 should be chosen; because it is the parent to 13 > 6 should be chosen; because it is the parent to 9 > 1 should be chosen; because it is the parent to 6 > > And that is it, because 1 has parent 0, and is therefore a > super-category. > > Right now I am doing this with PHP and a recursive function, but is it > possible to do this directly with one query? > > +----+-----------------------+----------+-----------+ > | id | name | priority | parent_id | > +----+-----------------------+----------+-----------+ > | 1 | DME | 999 | 0 | > | 2 | Training Material | 999 | 0 | > | 3 | RND | 999 | 0 | > | 4 | LEAP | 999 | 1 | > | 5 | TDI | 999 | 1 | > | 6 | Technical Support | 999 | 1 | > | 7 | Training Module | 999 | 1 | > | 8 | Detail by Component | 999 | 6 | > | 9 | Step/Process | 999 | 6 | > | 10 | Assessment | 999 | 9 | > | 11 | Design | 999 | 9 | > | 12 | Implement and Monitor | 999 | 9 | > | 13 | Evaluation | 999 | 9 | > | 14 | Reflection | 999 | 9 | > | 15 | Transition | 999 | 9 | > | 16 | TDI | 999 | 2 | > | 17 | LEAP | 999 | 2 | > | 18 | Other | 999 | 2 | > | 19 | Tools | 999 | 3 | > | 20 | R&D Document | 999 | 3 | > +----+-----------------------+----------+-----------+ > > Best regards, > Peter Lauri > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
