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]
