Quick way to get child categories?

[Nutter]

Let's say I have two tables - articles and categories.

categories
id - int auto_incr
parent_id int
description - char

articles
id - int auto_incr
parent_id int
article_text - text

The way it's set up is to allow subcategories. Is there a way to query the database and get all articles from a category and any of it's child categories?

Ex: Main -> News -> Local contains an article on Bob's new car. I'm browsing the category News and would like the car article to show up.

[aspdaddy]

If you know the id of news, say its 2 then just use:

select * from articles where cat_id in (select id from categories where parent_id=2)

If you need to go beyond 2 levsl of sub-category then it may be more efficient to write a code loop and run multiple SQL statements.

[Nutter]

That's what I wound up doing. I created a function that returns all children in an array and then recursively calls itself on each of the children adding the grandchildren to the array as well. The array is then imploded (PHP) back into a comma separated list to use in an IN query. I suppose with the correct indexes it should be pretty quick.