There is a table of the form:

id int not null parent int null ...

Where parent indicates the id of the parent. It is necessary for a given id to get a list of all descendants to the very last. It seems to be a common task, are there ready-made solutions or do you need to invent your own?

DBMS MS SQL 2012

    2 answers 2

     with Q as( select * from Tab where parent=10 union all select p.* from Tab p, Q where p.parent=Q.id ) select * from Q

    Like that. The first part, before union all should select the "seed" records from which to start the selection. The second part is recursive, for all records it is selected by the first part or it finds the following itself.

       SELECT parentName FROM parentTable AS p JOIN yourTable AS y ON y.parentId = p.parentId ;

      Here's an option if I understand you correctly

      • As I understand it - the number of descendants is not known. - Atlantis
      • No, you also need to get the descendants of all descendants, and so on. - NeC