T-sql gets leaf nodes
Based on the following table (I've kept the spaces between the lines for clarity)
Path
-----------
\node1\node2\node3
\node1\node2\node3\node5
\node1\node6\node3
\node1\node4\node3
\node1\node4\node3\node7
\node1\node4\node3\node8
\node1\node4\node3\node9
\node1\node4\node3\node9\node10
I want to get all paths containing a leaf node. So, for example, the following will be considered leaf nodes for the path \ node1 \ node4 \ node3
\node1\node4\node3\node7 \node1\node4\node3\node8 \node1\node4\node3\node9\node10
The following will be displayed below:
Output
---------------------------
\node1\node2\node3\node5
\node1\node6\node3
\node1\node4\node3\node7
\node1\node4\node3\node8
\node1\node4\node3\node9\node10
Pls. offer. Thanks.
+2
a source to share
2 answers
You want to find all leaves, which are all paths that are not contained in any other path. You can do this by checking for each path if there is another path that contains it, as follows:
SELECT Path FROM Table1 T1
WHERE NOT EXISTS (
SELECT NULL
FROM Table1 T2
WHERE T2.Path LIKE T1.Path + '\%'
)
Results:
Path
\node1\node2\node3\node5
\node1\node6\node3
\node1\node4\node3\node7
\node1\node4\node3\node8
\node1\node4\node3\node9\node10
+2
a source to share