[RESOLVED] Walking a tree with HeirarchyID - SQLServer 2012

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

So, basically what you are asking if you search for 1.3, then you want to get this result:
-- -- 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

?

In short - yes. 1.3 doesn't need to be a part of it, since I'll already have it.

If I can get it into this format:

ID -- RecId -- Name -- Description -- ParentID (or child ID, I'm not that picky)

Once I can get it like that, the UI will know how to render it correctly. It's just getting the sql to produce the right IDs so I can link them together.

-tg

Is the format of HeirarchyPath really like this "1.3.1.2", because then you should be able to get the data by simply doing a search like
SELECT * FROM your_table WHERE HeirarchyPath LIKE '1.3.%'

Ah...you've never worked with the HeirarchyID type before have you? Sadly, It doesn't quite work that way.

The HeirarchyID type is one of those funny types... you set it by giving it a tree value, eg "0/3/1/2" the 0 represents the root, the 3 represents the first child (to the root) with a node value of 3, and on down the line. But SQL Server doesn't store "0/3/1/2" .. because of course that would be too easy (and my my life a whole lot easier) ... instead it stores a hash value of some kind - "0x5C396E" Oh, and because it's a special type, it now has methods and functions...

Example:

sql Code:

-- Get the immediate children
select 
    LOCATION.ID,
    LOCATION.LOCID,
    LOCATION.NAME,
    LOCATION.DESCRIPTION,
    LOCATION.HIERARCHYPATH,
    (select PARENTLOC.ID from dbo.LOCATION as PARENTLOC where PARENTLOC.HIERARCHYPATH = LOCATION.HIERARCHYPATH.GetAncestor(1)) as PARENTID
from dbo.LOCATION
where (select PARENTLOC.ID from dbo.LOCATION as PARENTLOC where PARENTLOC.HIERARCHYPATH = LOCATION.HIERARCHYPATH.GetAncestor(1)) = @CONTEXTID

@CONTEXTID is a uniqueidentifier that's passed in (in the example it represents 1.3)

This is what lets me walk the tree upward:
LOCATION.HIERARCHYPATH.GetAncestor(1)

There is a GetDescendents (sp?) method, but it takes a start and end child hID, and returns all nodes along that path... which is close, but I don't know the end child and only really gets a path, it won't include siblings.

-tg

Well, I'll be... your idea did work... Turns out, that if you use .ToString() on a hID field, it unhashes the value and returns the original tree value ... from which I can then use a LIKE and get what I want.

Code:

---

SELECT
    LOCATION.ID,
    LOCATION.LOCID,
    LOCATION.NAME,
    LOCATION.DESCRIPTION,
    LOCATION.HIERARCHYPATH,
    (SELECT PARENTLOC.ID FROM dbo.LOCATION AS PARENTLOC WHERE PARENTLOC.HIERARCHYPATH = LOCATION.HIERARCHYPATH.GetAncestor(1)) AS PARENTID
FROM dbo.LOCATION
where LOCATION.HIERARCHYPATH.ToString() like (select LOCATION.HIERARCHYPATH.ToString() + '%' from LOCATIONwhere LOCATION.ID = @CONTEXTID)

---

I'm now getting exactly what I wanted.

Sometimes all you need is a shove in the right direction.

Thanks!

-tg

Hmm... interesting, I am glad I solved your problem :)

At work, we are still using SQL Server 2005, we will soon upgrade to 2012, and I can't wait to learn the new features.

Normally, when I do hierarchy trees, I put in the child the parent ID, this way, I can use CTE recursion (the "with" keyword), and find parent, children, etc...

Yeah, I used a CTE to walk it upwards, and was trying to do the same going down... which is where I was running into this problem. I had the immediate children... but couldn't figure out a smooth way to say, OK, I have these nodes, find the next set of nodes, where their parentIDs match the IDs of this set... repeat until we hit the bottom of the tree. I either kept getting jsut the immediate set, OR the recursive CTE blew up and trhowing an error (nesting too deeply).

Either way, I learned something new, and the query works perfectly.

-tg

I was playing with hierarchy id a while back. This is what I found.

Code:

---

declare @hid hierarchyid;

SELECT @hid = hid
FROM x12.Segment
WHERE id = 26;

-- get all ancestors
SELECT *
FROM x12.Segment
WHERE @hid.IsDescendantOf(hid) = 1
 AND @hid <> hid;

-- get all descendants
SELECT *
FROM x12.Segment
WHERE hid.IsDescendantOf(@hid) = 1;

---