With our new table structure, that really isn’t much work.
When you look at, for example, the 4-5 ‘Cherry’ node, you’ll see that the left values of all ancestors are less than 4, while all right values are greater than 5.
To display this tree like we did our recursive function, we’ll have to add an ORDER BY clause to this query.
If you add and delete rows from your table, your table probably won’t be in the right order.
As you can see, these numbers indicate the relationship between each node.
Because ‘Red’ has the numbers 3 and 6, it is a descendant of the 1-18 ‘Food’ node.
Therefore, we’ll have to use ‘lft’ and ‘rgt’ to identify the columns.
To get this path, we’ll need a list of all ancestors of that node.The last number is written at the right side of the ‘Food’ node.In this image, you can see the whole numbered tree, and a few arrows to indicate the numbering order. the left value of ‘Food’ is 1, the right value is 18).To get all ancestors, we can use this query: If you give me the left and right values of a node, I can tell you how many descendants it has by using a little math.The built-in hierarchyid data type makes it easier to store and query hierarchical data.
Hierarchical relationships exist where one item of data is the parent of another item.