t-sql getting leaf nodes

Question

Based on following table (I have kept spaces between the rows 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 the paths containing leaf node. So for instance, following will be considered leaf nodes for path \node1\node4\node3

\node1\node4\node3\node7
\node1\node4\node3\node8
\node1\node4\node3\node9\node10

The following will be the output:

Output
---------------------------
\node1\node2\node3\node5
\node1\node6\node3
\node1\node4\node3\node7
\node1\node4\node3\node8
\node1\node4\node3\node9\node10

Pls. suggest. Thanks.

Answer 1

A like clause would appear to do the trick:

select Path
from YourTable
where Path like '%\node3\%'
or Path like '%\node3'
or Path like 'node3\%'

Answer 2

You want to find all the leaves, which are all the paths which aren't contained in any other paths. You can do this by checking for each path whether 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