Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


HierarchyID for Multiple Root Nodes


HierarchyID for Multiple Root Nodes

Author
Message
Gayathri.Varadarajan
Gayathri.Varadarajan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 388
Hi,
I have the following scenario for which I am trying to implement the hierarchyid datatype:
Have an organization table which can have multiple root nodes and subsequent child nodes.
How do I proceed with such a scenario?

For Example:

Organization ParentOrganization
1 NULL
2 NULL
3 1
4 2
5 2
6 3
7 4

When I try using the hierarchyid::GetRoot for populating the organisations 1 and 2 I get the same hierarchyid and so I am unable to proceed.
Please suggest to proceed
Report post as abusive
Mike - CI
Mike - CI
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 1155
It seems to me that you need a higher level here. There should only be a single "Root". In this case you are trying to have two different roots. I may be wrong but it seems that thinking of a Hierarchy lilke a single tree with many branches, you must have a single root or essentially have different trees all together (separate table structure). The two organizations that you are trying to make as the root, I assume that they correlate somehow to one overarching "organization"? If so that should be the single root, and each of the two that you have now as the root should be the second level of the Hierarchy.
Gayathri.Varadarajan
Gayathri.Varadarajan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 388
Higher Level may not be a solution as per the functionallity .
Mike - CI
Mike - CI
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 1155
Any possibility of two different table structures that you could bring together through views?

Or perhaps you could create your own hierarchy structure that essentially works in the same fashion as the one in SQL 2008 but gives you the ability to have multiple roots.
Micah Ritchie
Micah Ritchie
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 125
Have you tried using a multi-field Primary Key?
Ray K
Ray K
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2600 Visits: 4556
Not sure whether or not this is applicable, but this sounds similar to the scenario presented in an article I read here a few weeks ago.

Here's the link to the article.

Hope this helps!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
Gayathri.Varadarajan
Gayathri.Varadarajan
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1090 Visits: 388
Ray K (12/3/2009)
Not sure whether or not this is applicable, but this sounds similar to the scenario presented in an article I read here a few weeks ago.

Here's the link to the article.

Hope this helps!


Thanks for the article.
This will be applicable when there is a definite number of known levels like parents.Levels that i may have to traverse is not specific, also cross apply is an approach to operate on the exisitng data model where in we have all the parent and child in the same table and a column to differentiate between them.Rather i want to use the datatype hierarchyid as it has its own advantages as easier access to nodes with specific functions and mainly memory management.
kevin.kraemer
kevin.kraemer
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 144
It sounds like you need to a "dummy" root node. You can then have your multiple roots be children of that one. The "dummy" root would not contain any actual info but just be used as the single root that is required. Your queries would need to exclude the "dummy" from being returned but it would be easy to determine that by using the GetRoot() method.
aaron.kempf 89506
aaron.kempf 89506
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 7
I don't understand.. Can't you just use Hierarchy combined with integer as the primary key?

For example, I'm using ChecklistID and ChecklistItemID in this example in order to store multiple parent child hierarchies.. in the same table.

CREATE TABLE [dbo].[ChecklistItems](
   [ChecklistID] [int] NOT NULL,
   [ChecklistItemID] [int] IDENTITY(1001,1) NOT NULL,
   [ChecklistItemName] [varchar](50) NOT NULL,
   [ChecklistItemParent] [int] NOT NULL,
   [OrderBy] [smallint] NOT NULL,
   [ChildLevel] [tinyint] NOT NULL,
   [ChildLevelDesc] [tinyint] NOT NULL,
   [ChecklistHierarchy] [hierarchyid] NULL,
   [HtmlPrefix] [text] NULL,
   [HtmlSuffix] [text] NULL,
CONSTRAINT [PK_ChecklistParentChild] PRIMARY KEY CLUSTERED
(
   [ChecklistID] ASC,
   [ChecklistItemID] ASC
)
)

Now I can easily generate a checklist hierarchy when I pass in a single checklistID into this sproc spCreateChecklist

CREATE procedure [dbo].[spCreateChecklist]
(
@checklistID int
)
as

with H(ChecklistID, ChecklistItemID, Level, ChecklistItemName, ChecklistItemParent, FQName)
As
(
Select ChecklistID, ChecklistItemID, 0, ChecklistItemName, ChecklistItemParent, Convert(varchar(max), ChecklistItemName)
From checklistparentchild
Where checklistItemID = checklistItemParent
and ChecklistID = @checklistID
UNION ALL

Select C.ChecklistID, C.ChecklistItemID, H.Level + 1, C.ChecklistItemName, C.ChecklistItemParent, Convert(varchar(max), H.FQName) + '.' + Convert(varchar(max), C.ChecklistItemName)
From checklistparentchild C
INNER JOIN H on H.checklistItemID = C.checklistItemParent
Where C.checklistItemID != C.checklistItemParent
and C.ChecklistID = @checklistID
)


Select *, SPACE(Level*3) + ChecklistItemName as IndentedName
From H
Order by FQName

Using Views, I think that this will be quite straight forward to navigate / flatten... of course, I've been using views to flatten parent-childs for a decade.

I just don't understand why you haven't considered this as an option

I'm at this post because I'm trying to change ChecklistItemID to ChecklistHierarchy in this example, I haven't been able to get my head around what you're having difficulty with, sorry.

-Aaron
MCITP: DBA SQL Server
ui ranejeb
ui ranejeb
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 130
A nice solution for traversing hierarchies with nodes having multiple parents.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search