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 | R&D 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]