I have a table that contains a field that is type HeirarchyID.
In fact the table looks like this:
ID - uiniqueidentifier
Name - Name of the record
Description - Description
RecID - user entered record number
HeirarchyPath - the Heirarchy ID for the data

So the table represents a hierarchical view of the structure of a location.

Code:
-- Location
-- -- Sub Location 1.1
-- -- -- sub sub location 1.1.1
-- -- -- -- And another location 1.1.1.1
-- -- -- -- And another location 1.1.1.2
-- -- -- sub sub location 1.1.2
-- -- -- -- And another location 1.1.2.1
-- -- -- -- And another location 1.1.2.2
-- -- -- -- And another location 1.1.2.3
-- -- -- -- And another location 1.1.2.4
-- -- -- sub sub location 1.1.3
-- -- -- -- And another location 1.1.3.1
-- -- -- -- And another location 1.1.3.2
-- -- -- -- And another location 1.1.3.3
-- -- Sub Location 1.2
-- -- -- sub sub location 1.2.1
-- -- -- -- And another location 1.2.1.1
-- -- -- -- And another location 1.2.1.2
-- -- -- sub sub location 1.2.2
-- -- -- -- And another location 1.2.2.1
-- -- -- -- And another location 1.2.2.2
-- -- -- -- And another location 1.2.2.3
-- -- -- -- And another location 1.2.2.4
-- -- -- sub sub location 1.2.3
-- -- -- -- And another location 1.2.3.1
-- -- -- -- And another location 1.2.3.2
-- -- -- -- And another location 1.2.3.3
-- -- Sub Location 1.3
-- -- -- sub sub location 1.3.1
-- -- -- -- And another location 1.3.1.1
-- -- -- -- And another location 1.3.1.2
-- -- -- sub sub location 1.3.2
-- -- -- -- And another location 1.3.2.1
-- -- -- -- And another location 1.3.2.2
-- -- -- -- And another location 1.3.2.3
-- -- -- -- And another location 1.3.2.4
-- -- -- sub sub location 1.3.3
-- -- -- -- And another location 1.3.3.1
-- -- -- -- And another location 1.3.3.2
-- -- -- -- And another location 1.3.3.3
-- -- Sub Location 1.4
-- -- -- sub sub location 1.4.1
-- -- -- -- And another location 1.4.1.1
-- -- -- -- And another location 1.4.1.2
-- -- -- sub sub location 1.4.2
-- -- -- -- And another location 1.4.2.1
-- -- -- -- And another location 1.4.2.2
-- -- -- -- And another location 1.4.2.3
-- -- -- -- And another location 1.4.2.4
-- -- -- sub sub location 1.4.3
-- -- -- -- And another location 1.4.3.1
-- -- -- -- And another location 1.4.3.2
-- -- -- -- And another location 1.4.3.3
Of course it's not as balanced as all that, but should give you an idea.
So what I'm trying to do now is display a chunk of that. Displaying the whole tree isn't an issue, got that covered.
Let's say they user selects Sub Location 1.3 ... I want to be able to see where in the tree it is (getting its ancestors) - this I have covered.
Where I'm running into trouble is walking the tree DOWNwards... I want to start with Sub Location 1.3 and display its children and their children, and own down.

I can get the query that starts with the selected node (for lack of a better term) and display its ancestry.
I can get a query that displays the immediate children of the selected node.

What I can't get is grandchildren and great-grandchildren.

-tg