MySQL Is it possible to get all subitems in a hierarchy?

Question

I looked at this:

Select products in which a category belongs to any category in the hierarchy.

And we are talking about CTE, which is not in MySQL. I have a structure like:

category_id | parent_category_id | name

I want to get all the subcategory identifiers of a particular category_id. Is it possible without grabbing the level and then sorting through them?

Answer 1

Is this just an Adjacency Model table? Then this is not possible in a single request without knowing the maximum depth.

Food for Thought Managing hierarchical data in MySQL (although I'm not a fan of using a nested dataset model for data that changes regularly).

() , : , , . "" , .

: /, / "" node id (: '12/62/28/345 ', / node (345 28, 28 62 ..)), (/ ):

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>;