[foam] all ultimate child nodes of Sql query tree structure

Background

In the project, there is a free form storage table, that is, unfixed columns, perhaps 2 columns, 3 columns, 4 columns. But the last two columns are the results and comments, where the data needs to interact.

In a dynamic table, each cell stores a row to record the parent-child relationship.

Demand

According to the template data column, it is parsed and stored in the data result table.

problem

There are only 12 rows in the result row and 28 rows in the dynamic table. How to select the result cell and store it?

Analysis.

In the dynamic table, the tree-level ultimate child node is 12 rows, which matches the rows of template data.

Technical point

How to get all the ultimate child nodes of the tree structure at once.

Source code select * from ITEM where id not in (select parentId from ITEM where parentId is not null)

In the above sentence, the core is that not in is simple and direct, the existence of the parent field is excluded, and the rest are all the ultimate child nodes. This reverse thinking is absolutely awesome.

You can add your own conditions at will.

Thank you for the above!