selecting all subcategories

[Q1712]

this was brought up by theBrent.

but i try to rephrase here, as i would like to know the answer as well.

i have a MySQL table containing categories having a “foreign”-key to itself.
so that evey subcategory hold the id of the parentcategory. that way it’s posible to have as many subcategories as i want.

example:

| id | parent_id | name
|  1 |      NULL | world
|  2 |         1 | europe
|  3 |         1 | amerika
|  4 |         3 | usa
|  5 |         4 | tennesy
|  6 |         5 | memphis
|  7 |         2 | germany
|  8 |         7 | w?rtemberg
|  9 |         8 | stuttgart
| 10 |         8 | schwenningen a.n.

structure:

world
 * amerika
   - tennesy
     . memphis
 * europe
   - germany
    . w?rtemberg
        stuttgart
        schwenningen a.n

now i wanna have one SQL statement selecting all subcategories e.g. of germany.

may anybody help?

[Zyppora]

Since the depth of the subcategories is variable, there is no telling how the query should look. If you only want the first-level subcategories, the following will do:

SELECT * FROM categories AS a, categories AS b WHERE a.id = b.parent_id AND a.name = 'germany'

If you want to go to the deepest level, you should consider using recursive functions or loops.

[Q1712]

dass wei? ich auch.

i always thought that SQL is much stronger than i know, is there realy no way to do recursion with an SQL-query?

[Zyppora]

Not as far as I know. Perhaps you can do some magic using stored procedures?