List:General Discussion« Previous MessageNext Message »
From:Gokhan Demir Date:May 18 2006 7:11pm
Subject:RE: Recursive query
View as plain text  
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:peter@stripped] 
Sent: Thursday, May 18, 2006 8:20 PM
To: mysql@stripped
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/mysql?unsub=1

Thread
Recursive queryPeter Lauri18 May
RE: Recursive queryGokhan Demir18 May
  • RE: Recursive query => Nested SetDaevid Vincent18 May
Re: Recursive queryPeter Brawley18 May