mysql query; select all items in category tree

Question

I have a table with categories, where a category can have a parentid (is child of other category). When i want to filter items on a parent category id, I want the query to include all child categories.

Lets say I have this category structure:

id || title     || parentid
1  || Sports    || 0
2  || Tennis    || 1
3  || Wimbledon || 2

Now I have an article in the category Wimbledon. And I want to display all articles in the category sport.

SELECT item.* FROM #table_items AS item WHERE item.catid = 1;

The above query doesn't return the article in the category Wimbledon. Is this possible?

Answer 1

SELECT item.* FROM #table_items AS item,#categories as catg
WHERE item.parentid = cat.parentid;

Answer 2

I got the solution I was searching for. Lets say i want to display all items in a category with id 12:

SELECT item.* FROM #table_items AS item
LEFT JOIN #__foc_shop_categories AS cat1 ON (cat1.id = item.catid)
LEFT JOIN #__foc_shop_categories AS cat2 ON (cat1.parentid = cat2.id)
... (and so on for more depth)

WHERE (cat1.id = 12 OR cat2.id = 12 OR ...)
GROUP BY item.id

It results all items in the category and the nested categories.