Select Hierarchy Node Parent ID

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)

Select Hierarchy Node Parent ID

Author

Topic

hurdy
Starting Member

17 Posts

Posted - 2011-10-20 : 20:45:25

Hi there,

Firstly, thanks for the time taken out to read this.

I have a hierarchy table with columns containing ID, Name, Hierarchy

I need an SQL Statement that will get the ID of each row according to its hierarchial ancestor.

For example, imagine I have the following data in my table named Parks:

ID      Name       Hierarchy
1       John         /1/
2       Chris        /1/1/
3       Simon        /2/
4       David        /1/2/
5       Joe          /2/1/

so I can create an SQL Query which gets the ancestors of each Hierarchy item like so:


SELECT ID, Name, Hierarchy, Hierarchy.GetAncestor(1) AS Ancestor
FROM Parks

which displays the following data:


ID        Name        Hierarchy       Ancestor
1         John        /1/             /
2         Chris       /1/1/           /1/
3         Simon       /2/             /
4         David       /1/2/           /1/  
5         Joe         /2/1/           /2/

however, I want the ancestor to reflect the ID value rather than the hierarchial path, like so:


ID        Name        Hierarchy       Ancestors ID
1         John        /1/             NULL
2         Chris       /1/1            1
3         Simon       /2/             NULL
4         David       /1/2/           1  
5         Joe         /2/1/           3

Any advice on how to achieve this would be very much appreciated.

Thank you again.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-20 : 20:58:04

Why is Joe's Ancestor ID 3 and not 2?

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

hurdy
Starting Member

17 Posts

Posted - 2011-10-20 : 21:03:58

Hi Vinnie,

It's because I need the ID value of its parent. Joe's parent node is Simon which has an ID of 3. :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-20 : 21:04:42

[code]
select p.ID, p.Name, p.Hierarchy,
Ancestor = left(p.Hierarchy, len(p.Hierarchy) - charindex('/', reverse(p.Hierarchy), 2) + 1),
AncestorID = a.ID
from Parks p
left join Parks a on a.Hierarchy = left(p.Hierarchy, len(p.Hierarchy) - charindex('/', reverse(p.Hierarchy), 2) + 1)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

hurdy
Starting Member

17 Posts

Posted - 2011-10-20 : 21:11:21

Hi khtan,

Thanks for your input. When I exectue your query I get the following Error:

Argument data type hierarchyid is invalid for argument 1 of len function.

Not quite sure what this mean. Please excuse me, I'm a newbie to SQL Queries.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-20 : 21:16:53

what is the data type for that column ?

KH
[spoiler]Time is always against us[/spoiler]

hurdy
Starting Member

17 Posts

Posted - 2011-10-20 : 21:19:06

When I paste your code into VS2010 sql query it adjusts it the the following:


SELECT     p.ID, p.Name, p.Hierarchy, LEFT(p.Hierarchy, LEN(p.Hierarchy) - CHARINDEX('/', REVERSE(p.Hierarchy), 2) + 1) AS Ancestor, a.ID AS AncestorID
FROM       Parks AS p LEFT OUTER JOIN
           Parks AS a ON a.Hierarchy = LEFT(p.Hierarchy, LEN(p.Hierarchy) - CHARINDEX('/', REVERSE(p.Hierarchy), 2) + 1)

ID = int
Name = nvchar(60)
Hierarchy = Hierarchyid

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-20 : 21:22:09

looks like it is a User Define Data Type
can you execute this and post the result ?


exec sp_help Hierarchyid

KH
[spoiler]Time is always against us[/spoiler]

hurdy
Starting Member

17 Posts

Posted - 2011-10-20 : 21:26:43

The followig errors we encountered while parsing the contents of the SQL Pane:
The EXEC SQL construct or statement is not supported.

However I get the following results:
Type_name: hierarchyid
Storage_type: NULL
Length: 892
Prec: 892
Sacle: NULL
Nullable: Yes
Default_name: none
Rule_name: none
Collation: NULL

hurdy
Starting Member

17 Posts

Posted - 2011-10-20 : 21:27:32

[url]http://msdn.microsoft.com/en-us/library/bb677290.aspx[/url]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-20 : 21:39:30

oh, sorry that is the new hierarchy data type


select	p.ID, 
	p.Name, 
	Hierarchy	= p.Hierarchy.ToString(), 
	Ancestor	= p.Hierarchy.GetAncestor(1).ToString(),
	AncestorID	= a.ID
from	Parks p
	left join Parks a 	on	a.Hierarchy	= p.Hierarchy.GetAncestor(1).ToString()

KH
[spoiler]Time is always against us[/spoiler]

hurdy
Starting Member

17 Posts

Posted - 2011-10-20 : 21:44:30

That's the boy! Thanks khtan, your help is greatly appreciated!!

As a side question, do you know of any good books where I can learn SQL syntax?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-20 : 21:52:10

The last books on SQL that i read was ages ago.

Maybe you can try some of the books for beginners here
http://www.sqlteam.com/store.aspx

KH
[spoiler]Time is always against us[/spoiler]

hurdy
Starting Member

17 Posts

Posted - 2011-10-20 : 22:04:15

thanks for the advice :), and again thanks for your help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-20 : 22:16:47

welcome

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-20 : 22:52:49

quote:

[i]Originally posted by hurdy


ID        Name        Hierarchy       Ancestor
1         John        /1/             /
2         Chris       /1/1/           /1/
3         Simon       /2/             /
4         David       /1/2/           /1/  
5         Joe         /2/1/           /2/

I was asking why joes ancestor is was 3, because it appeard that based on the data above it should be 2.

Maybe it was just a typo.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-20 : 22:56:55

Joe's ancestor is hierarchy /2/

Hierarchy /2/ is Simon. Simon's ID is 3

KH
[spoiler]Time is always against us[/spoiler]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-10-20 : 22:58:49

Ohh, my bad, it was a long day... Chalk this up to my 15 hour terrible work day:)

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources