there is a table
**page** - id - name - cat
**cat** - id - title - parent_id (NULL)
let’s say a category is selected, you need to display the last (id DESC) pages (page)
with a limit (LIMIT) from the subcategories of this category (well, respectively, from this cat.)
The query will select all posts that match category 123 and its subcategories by 1 nesting level.
SELECT * FROM page p LEFT JOIN cat ON c.id = p.cat WHERE (p.cat = 123) OR (c.parent_id = 123) ORDER BY p.id DESC LIMIT 10
Two levels of nesting:
SELECT * FROM page p LEFT JOIN cat 1 ON c.id = p.cat LEFT JOIN cat 2 ON c2.id = c1.parent_id WHERE (p.cat = 123) OR (c1.parent_id = 123) OR (c2.parent_id = 123) ORDER BY p.id DESC LIMIT 10
Unfortunately, this type of tree does not allow you to select the entire nested tree depth-wise without adding a LEFT JOIN at each next level.