Get all descendants in one query?

Question

I'm creating an e-commerce site where I have the category_id in the URL and based on the category_id I'll query all the children and grandchild (if found) so I can display products only related to that category_id. This query is really cool because it doesn't care about how many parent's it has ... it just looks for all the parents until it doesn't find any more.

The problem I'm having is my query will only get parent and grand parents (if found), but I cannot seem out how to make this query get all children rows or grandchildren if found. This query was pulled from here, so I did not write the query and it's hard for me to make the correct changes ... I tried all day yesterday.

Edit This query will also NOT get ALL the related children and grandchildren (if found). Where I want ALL children and grand children (if found).

Edit Here's the SQL Fiddle where my data can be found.

SELECT T2.category_id, T2.category_name 
FROM (SELECT @r AS _id, (SELECT @r := parent 
FROM categories 
WHERE category_id = _id) AS parent 
FROM (SELECT @r := 182)vars, categories h 
WHERE @r <> 0) T1 
JOIN categories T2 
ON T1._id = T2.category_id 
ORDER BY category_id ASC

Here's my table schema:

category_id | category_name | parent

Answer 1

There is an alternate way to accomplish this, but it requires a second hierarchy table.

The additional table contains one row per parent-direction relationship, as in:

category_id category_name parent
   1           cat1          1
   2           cat2          1
   3           cat3          1
   4           cat4          2
   5           cat5          3
   6           cat6          5

category_id ancestor
   1           1
   2           1
   3           1
   4           1
   4           2
   5           1
   5           3
   6           1
   6           3
   6           5

This table can be maintained by your code or by a trigger. It makes selecting an entire hierarchy trivial and very fast, but adds some maintenance (addition/deletion) overhead, so you'll need to consider the long-term costs vs overhead.