The first thing I’d do is structure the database to appropriately manage a hierarchical structure. Once you’ve picked the appropriate data model, then the descendant queries become quite easy.
Briefly, there are basic architectural models that have been identified for optimizing the storage of a hierarchical data structure within a relational database that use “sideband” data to help manage the structure. Each of those architectural models have different advantages and disadvantages - it’s up to you to understand those benefits and select the right architecture for your specific application.
See discussions at: