4

How would I go about querying for only records that are "only leaf nodes" (ie. no children)?

I have tried a query like this:

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

But this still seemed to return some nodes that had children.

I am using the built-in hierarchyid data type (part of ms sqlserver)

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Marty
  • 2,965
  • 4
  • 30
  • 45
  • I new I had seen this query before and I have found a scenario where it fails. Have a look at [this answer](http://stackoverflow.com/a/8562690/569436). BTW, The new answer I have provided is pretty much the same as the answer by @AaronBertrand. – Mikael Eriksson May 22 '12 at 06:54

1 Answers1

7
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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490