CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
stackoverflowuser
stackoverflowuser

Reputation: 22380

t-sql getting leaf nodes

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.

Upvotes: 2

Views: 503

Answers (2)

Mark Byers
Mark Byers

Reputation: 838376

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

Upvotes: 2

Andomar
Andomar

Reputation: 238126

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\%'

Upvotes: 0

Related Questions