Getting the bottom nodes of a hierarchy

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting the bottom nodes of a hierarchy

Author

Topic

DashboardMan
Starting Member

2 Posts

Posted - 2011-06-26 : 07:49:20

I am using hierarchyID, and I am trying to find all the nodes at the bottom of the hierarchy. For example if the hierarchy is:

A

AA

AAA

AAAA <

AAAB <

AB

ABA

ABAA <

ABAB <

Then the query would return:

AAAA

AAAB

ABAA

ABAB

Any suggestions?

DashboardMan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-26 : 08:47:50

If you are looking for all nodes that don't have any children, you should be able to do it like this:

 select 
    *
 from
    YourTable y1
 where 
    not exists 
    (
       select * 
       from YourTable y2 
       where y2.HierarchyIdColumn.GetAncestor(1) = y1.HierarchyIdColumn
    )

Is that what you are looking for, or are you looking for nodes at a specific level? If you are, a where clause such as "WHERE HierarchyIdColumn.GetLevel()=4" should be able to get that information.

DashboardMan
Starting Member

2 Posts

Posted - 2011-06-26 : 09:33:16

That's it!

Thanks for the quick response.

DashboardMan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources