Geek asks and answers

Find all leaf node entries using hierarchy

How can I apply for queries only for records that are “leaf nodes only” (i.e. no children)?

I tried a query like this:

select *
from TableA tt
where tt.HierarchyId.GetDescendant(null, null) not in 
(
    Select  t.HierarchyId
    from TableA t
)

But it still returned some nodes that had children.

I use the built-in hierarchy data type (part ms sqlserver)

+3
source share
1 answer
SELECT A.HieracrchyId, A.HierarchyId.ToString()
  FROM dbo.TableA AS A 
  LEFT OUTER JOIN dbo.TableA AS B
  ON A.HierarchyId = B.HierarchyId.GetAncestor(1)
  WHERE B.HierarchyId IS NULL;
+6
source

More articles: