How to combine requests to get products from a category and its subcategory?

Question

SQL

Hello everyone, there is such a request that takes products from all sub-subcategories, this is necessary for a subcategory. I realized that it does not take products from the subcategory itself, but only from all subsubcategories, I need to show products from both the subcategory and its subsubcategories.

Simply put, all products of the selected subcategory and subsubcategories! I will be very grateful!

SELECT DISTINCT g.* FROM products g JOIN subsubcategories c ON c.id = g.category_id WHERE c.parent_id = :category_id AND g.type_category = 'subsub'

Answer 1

join explicitly n times, where n is the height of the tree
recursive cte
change tree storage schema