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 | RD 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]
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 | RD 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]
Re: Recursive query
Peter, Right now I am doing this with PHP and a recursive function, but is it possible to do this directly with one query? SQL is not recursive, so you need an sproc. There are some examples with discussion at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. PB - Peter Lauri wrote: 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 | RD Document | 999 | 3 | ++---+--+---+ Best regards, Peter Lauri -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.6.0/341 - Release Date: 5/16/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Recursive query = Nested Set
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=3453open=1anc=0view=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 | RD 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]
recursive query
hi, how can i make recursive querys in mysql? i want to show a tree. my table: tree (id int, parent int, name char(10)); id parent name -- 1 0 joe 2 1 mike 3 1 jim 4 2 george 5 3 eric 6 4 steven and i need this result: id parent name --- 1 0 joe 2 1 mike 4 2 george 6 4 steven 3 1 jim 5 3 eric the tree: 1 0 joe -2 1 mike --4 2 george ---64 steven -3 1 jim --5 3 eric thanks ati. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: recursive query
http://marc.theaimsgroup.com/?l=mysqlm=99213870412899w=2 http://marc.theaimsgroup.com/?l=mysqlm=99214323317893w=2 http://www.bolt.cx/misc/thread.txt hi, how can i make recursive querys in mysql? i want to show a tree. my table: tree (id int, parent int, name char(10)); ... thanks ati. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
