sql - MySQL - Is it possible to get all sub-items in a hierarchy? -

I have seen it:

And this is CTE, which is MySQL I do not exist in: I have the structure:

  category_id | Parent_category_id | Name  

I want to get all the sub-categories ID of a given category_id Is it possible that without a single level grab, then looping between those people?

Is this just a soldier sustainability model table? This is not possible in a query without knowing the maximum depth.

Food for thought (although I do not advise to use nested set model for regularly changing data).

Many (left) more specifically connects: as much as the left side of the tree, it is possible in a query. This is the reason that many people do to protect the 'depth' of a particular category, so that you can filter and limit it to add more amounts to the same table.

Personally, to change the data regularly: I configure the trigger on an insert / update, which will cache / cache the current 'path' of the node based on ID (e.g. For: Path '12 / 62/28 / 345 ', in which the step between the delimiters / is the primary key of the parent node in the correct order (Parents 28, 28, Parents 62 of 345, Etc.), so I can query it joins just like one (in the form of a separator) Used):

  SELECT j. * JOIN Tablename j Tablet with WHERE j.Path like Konkat (Opit, '/%') and j.id! = O.id - leaving the parents asked. WHERE OAD = & lt; You are viewing the ID of the node;