Prev: How to substitute for a non-existing column in a joined table
Next: DBCC MemoryStatus memory_node_id = ...
From: Sam on 21 Apr 2010 20:13
Hi,

For some reason I've never gotten used to hierarchyid way of doing things.

Here's what I want to do: I want to specify a node and want my select
statement to return that node's child nodes that are directly under that node.

For example:

Root
Level 1
Level 1.1
Level 1.2
Level 1.2.1
Level 1.2.2
Level 2
Level 3

So, if I set my input parameter to root, I only want to get Level 1, Level 2
and Level 3 -- not all the sub levels under Level 1.

If I set my input parameter to Level 1, I only want to get Level 1.1 and
Level 1.2.

How do I do this?
--
Thanks,

Sam
From: Plamen Ratchev on 21 Apr 2010 22:01
You need to join an instance to get the root node (or Level 1) with another instance that checks the nodes if they are
direct subnodes using the GetAncestor(1) method:

SELECT S.hierarchyid_column, S.node
FROM Nodes AS R
JOIN Nodes AS S
ON R.node = 'root'
AND S.hierarchyid_column.GetAncestor(1) = R.hierarchyid_column;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Sam on 21 Apr 2010 23:45
Plamen,

As always, you've been a great help. Thank you so very much.

--
Thanks,

Sam


"Plamen Ratchev" wrote:

> You need to join an instance to get the root node (or Level 1) with another instance that checks the nodes if they are
> direct subnodes using the GetAncestor(1) method:
>
> SELECT S.hierarchyid_column, S.node
> FROM Nodes AS R
> JOIN Nodes AS S
> ON R.node = 'root'
> AND S.hierarchyid_column.GetAncestor(1) = R.hierarchyid_column;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
 | 
Pages: 1
Prev: How to substitute for a non-existing column in a joined table
Next: DBCC MemoryStatus memory_node_id = ...