I have looked at this:
Select products where the category belongs to any category in the hierarchy
And it is talking about CTE, which doesn't exist in MySQL. I have structure like:
category_id | parent_category_id | name
I want to retrieve all the sub-categories ids of a given category_id. Is this possible without grabbing a tier, then looping through those?
This is just simpy a Adjacency Model table? Then it is not possible in one query without knowing the maximum depth.
Food for thought is Managing Hierarchical Data in MySQL (although I don't advocate using the Nested Set Model for data that alter regularly).
With a lot of (left) joins, more specifically: with as many left joins as the maximum depth of the tree, it will be possible in one query. This is the reason a lot of people tend to save the 'depth' of a specific category, so you 'll be able to filter and limit the amount of joins to the same table to a more sane amount.
Personally, for regularly altering data: I tend to configure a trigger on an insert / update, which will save / cache the current 'path' of a node based on id's (for instance: a path is '12/62/28/345', in which every step between the delimiter / is the primary key of a parent node in the correct order (345's parent is 28, 28's parent is 62, etc.)), so I can query it with just one join like this (/ used as separator):
SELECT j.*
FROM tablename o
JOIN tablename j
WHERE j.path LIKE CONCAT (o.path,'/%')
AND j.id != o.id -- skip parent asked for.
WHERE o.id = <the id of the node you're looking for>;
