Expanding Hierarchies......with a twist
-
I have a bit of a twist on the BOL example of expanding Hierarchies. It's a real brain melter for me so I hope someone out ther can lend me a hand.
I found the example in BOL helpful but what quest I really need answered is this: If I supply you with two parameters (eg: 9 and 10). Can you tell me if 9 is a parent of 10. I can get this answer if I just pass in 9 and look at the result of the tree, but I need it to be a little more robust than that by beian able to handle two parameters and telling me where they fall in the tree. Based off of this I can pass back a literal string of Child, Sibling or other(Not related): Here is the code to create the table, insert statements and the modified code from BOL that currently accepts one parameter:
---Table Creation
CREATE TABLE Hierarchy
(Parent int null,
Child int not null
CONSTRAINT UIX_ParentChild
UNIQUE NONCLUSTERED (Parent,Child))
CREATE CLUSTERED INDEX CIX_ParentON Hierarchy(Parent)
GO
---Insert statmentsINSERT Hierarchy VALUES(Null,1)INSERT Hierarchy VALUES(1,8)
INSERT Hierarchy VALUES(8,9)
INSERT Hierarchy VALUES(9,10)
INSERT Hierarchy VALUES(9,11)
INSERT Hierarchy VALUES(9,12)
INSERT Hierarchy VALUES(1,13)
INSERT Hierarchy VALUES(13,14)
INSERT Hierarchy VALUES(13,15)
Insert Hierarchy VALUES(1,21)
INSERT Hierarchy VALUES(1,20)
GO
---Modified BOL queryDECLARE @current intDECLARE @lvl int ---Level in the Hierarchy
DECLARE @line char(20)
Select @current = 8 -------Starting point where you want to look at the HierarchyCREATE TABLE #stack (item int,lvl int
 
INSERT INTO #stack VALUES (@current,1)SELECT @lvl = 1
--select * from #stack--drop table #stack
WHILE @lvl > 0BEGIN
IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
BEGIN
SELECT @current = item
FROM #stack
WHERE lvl = @lvl
SELECT @line = space(@lvl - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE lvl = @lvl
AND item = @current
INSERT #stack
SELECT Child, @lvl + 1
FROM Hierarchy
WHERE parent = @current
ORDER BY Child DESC
IF @@ROWCOUNT > 0
SELECT @lvl = @lvl + 1
END
ELSE
SELECT @lvl = @lvl - 1
END -- WHILE
drop table #stack
-
Hey,
Check out if this SQL stored proc is what you are looking or helps you get there:
create procedure dbo.usp_getItemLevels(@item1 integer, @item2 integer)
as
begin
create table #hierarchy_levels (
parent integer,
item integer,
level integer )
-- **** Determining all "root" levels ****
insert into #hierarchy_levels(parent, item, level) select null, child, 0 from hierarchy where parent is null
-- **** Determing all "sibling" levels ****
declare @parent_level as integer, @rows_found as integer
set @parent_level = 0
while (1 = 1)
begin
insert into #hierarchy_levels (parent, item, level)
select
parent, child, level = @parent_level + 1
from
hierarchy
where
parent in (select item from #hierarchy_levels where level = @parent_level)
set @rows_found = @@rowcount
-- exit loop when no more rows found for @parent_level
if (@rows_found = 0) break
-- look at "next" parent level
set @parent_level = @parent_level + 1
end
-- **** Getting/Outputting results ****
declare @parent1 as integer, @level1 as integer
declare @parent2 as integer, @level2 as integer
select @parent1 = parent, @level1 = level from #hierarchy_levels where item = @item1 group by parent, level
select @parent2 = parent, @level2 = level from #hierarchy_levels where item = @item2 group by parent, level
declare @relation as varchar(255)
set @relation =
case
when ( @parent2 = @item1 ) then 'Child' -- @item2 is child of @item1
when ( @parent1 = @item2 ) then 'Parent' -- @item2 is parent of @item1
when ( @parent2 = @parent1 ) then 'Sibling' -- @item2, @item1 have same parent
else 'Other' -- for other relationships / non-relationship between @item1, @item2
end
select @relation as relation, @level1 as item1_level, @level2 as item2_ level
end
Regards,
JP
-
Thank you for your response JP, I believe this will get me closer to what I was looking for, a thousand thanks.
To give you a little more background, what I would like the two items to represent is this:
@item 1--The level of the user that is being updated
@item2-- The level of the user performing the action
So in a given scenario, Jon Doe(belongs to level 10) is wanting to update another users record in the same level(level 8). But since Jon is a child of the updated record I'd like the output to look like the following:
Child 8,10 (Jon cannot update a parent)
Another example would be
Sibling 9,10 (9 and 10 are children of 8)
So the relationships I'd like to passback are child, sibling, other(Like passing in two un-related levels eg: 20 and 8,(same root parent but 20 is not a child of 8).
So how can I change the code without mangling this fine piece of work you've provided for me?
Demico Q.
-
Hey Demico,
I edited the SQL proc in my previous post based on the info in your last reply (refer to my previous post for the edited code, it is in bold).
Just an FYI:
In your latest post, if I use the table values inserted in your original post then
- 8 is the grandparent of 10
- 9 is the child of 8 and 10 is the grandchild of 8
- 20 and 8 are siblings since they have the same parent 1
Regards,
JP
-
Thank you very much for your help on this JP. Your solution "fits the bill".
Demicoq
-
Hi JP
Using the solution for finding levels I am able to go up the tree to find the grand parent of the id I provide. So far I have three levels and a simple sub select works well finding the grandparent. The probelm I'm having is not being able to go up the tree if I say....have 4 or 5 levels found in the hierarchy. Do you have any suggestions for creating some code that doesn't care where you start in the hierarchy it will always find it's great grandparent?
Note: All parents whether grand or great will be found in level 1.
demicoq
-
Hey Demicoq,
Try to see if this is what you are looking for:
create procedure dbo.usp_FindParentOf
(
@item integer,
@parent_relation varchar(50) = 'parent'
)
as
begin
-- table to store the parent level_counters
declare @parent_level_counters table (
parent_relation varchar(50),
level_counter integer
)
-- populating table with all the parent relationships interested in finding
-- (note: keep on insert parent levels as needed)
insert into @parent_level_counters values('parent', 1)
insert into @parent_level_counters values('grandparent', 2)
insert into @parent_level_counters values('great grandparent', 3)
insert into @parent_level_counters values('great great grandparent', 4)
-- retrieve the level_counter of the parent relation to find
declare @parent_level_counter as integer
select @parent_level_counter = level_counter from @parent_level_counters where parent_relation = @parent_relation
declare @level_counter as integer, @parent as integer, @rows_found as integer, @temp_item as integer
set @level_counter = 0
set @rows_found = -1
set @temp_item = @item
-- loop to find the item in hierarchy that is the @parent_relation of @item
while ( @level_counter < @parent_level_counter and @rows_found != 0 )
begin
-- get the 'parent' of @item
select @parent = parent from hierarchy where child = @temp_item
-- keep track of how many rows found (this is so that loop "immediately" exits in
-- cases when no more parents found before reaching @parent_level_counter)
set @rows_found = @@rowcount
-- now, set @temp_item to @parent to move up the hierarchy
set @temp_item = @parent
-- increment counter to move up 1 level
set @level_counter = @level_counter + 1
end
-- returning the results
declare @out_msg as varchar(255)
set @out_msg =
case when ( @level_counter = @parent_level_counter ) then
cast(@parent as varchar) + ' is the ' + @parent_relation + ' of ' + cast(@item as varchar)
else
'No ' + @parent_relation + ' found in hierarchy for ' + cast(@item as varchar)
end
select @out_msg as result
end
-
This is close but what I need it do is always find the highest level in the hierarchy where the type is = 1. It just so happens that Type indirectly corresponds to the levels that the query you supplied in your earlier posts finds. (#Hierachy_levels)
For example if I supply a domain of 12 I should get back a domainID of 13, which is a great grandparent of domainID 24. 24 is the DomainID of domain 12. So the chain would be 17 is the parent of 24, 14 is the grand parent of 24, and 13 is the great grandparent of 24.
I've included the table creation and insert statements to reproduce
CREATE TABLE [dbo].[Domain] (
[DomainID] [int] IDENTITY (1, 1) NOT NULL ,
[DomainTypeID] [int] NOT NULL ,
[DomainName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Domain] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DomainParent] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Domain] ADD
CONSTRAINT [PK_Domain] PRIMARY KEY CLUSTERED
(
[DomainID]
 
ON [PRIMARY] ,CONSTRAINT [TC_Domain_Unique_DomainID] UNIQUE NONCLUSTERED
(
[DomainID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[Domain] ADD
CONSTRAINT [FK_Domain_Domain] FOREIGN KEY
(
[DomainParent]
 
REFERENCES [dbo].[Domain] ([DomainID]
 
Inserts:
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(1,'SUPPORT','SUPPORT',NULL)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(1,'H3019','20',1)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(2,'Dr. George AbersolNetwork','2',8)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(3,'Dr. Demico Quinn','3',9)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(3,'Dr. David Reyes','4',9)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(3,'Dr.Ears,Nose and Throat','5',9)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(1,'H3021','21',1)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(2,'Plastic surgeryNetwork','6',13)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(3,'Dr.Scartissue','7',14)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(3,'Dr.Botox','8',14)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(1,'H3022','22',1)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(1,'H3023','23',1)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(2,'Dermatology Network','9',18)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(3,'Dr.Acne','10',21)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(3,'Dr.Phisoderm','11',21)
INSERT INTO [domain] ([DomainTypeID],[DomainName],[Domain],[DomainParent])VALUES(4,'IndDoctorbotox','12',17)
-
Quick note: Here is the modified script to go with the script you provided me with last week.
DECLARE @domain varchar(80)
set @domain = '12' --This guy happens to have a parent of 9(network) and a grandparent of 8 which is a hospital.
create table #hierarchy_levels2 (
parent integer,
item int,
domaintypeID int,
domain varchar(80),
Domain_level integer )
--Drop Table #hierarchy_levels2
-- **** Determining all "root" levels ****
insert into #hierarchy_levels2(parent,domaintypeid,item, domain, domain_level)
select null, domaintypeid,domainid, domain, 0
from Domain
where DomainParent is null
select * from #hierarchy_levels2
-- **** Determing all "sibling" levels ****
Declare @parent_level as integer
Declare @rows_found as integer
set @parent_level = 0
while (1 = 1)
begin
insert into #hierarchy_levels2 (parent, domaintypeid,item, domain, Domain_level)
select
DomainParent, domaintypeid, domainid, domain, Domain_level = @parent_level + 1
from
Domain
where
Domainparent in (select item from #hierarchy_levels2 where Domain_level = @parent_level)
set @rows_found = @@rowcount
-- exit loop when no more rows found for @parent_level
if (@rows_found = 0) break
-- look at "next" parent level
set @parent_level = @parent_level + 1
end
--Bring back the parent and or grandparent
select domainparent
from domain
where domainid = (select Parent
from #hierarchy_levels2
where domain = @domain AND Domain_level <> 0 and domaintypeid = 1)
-
Any luck with this question J.P.?
-
Ok figured it out. Here is the solution.(one way to solve that is.......)
DECLARE @domain varchar(80)
DECLARE @domainid int
Select @domain = '7'
--Get the domainid of the domain that is being passed in.
SELECT @domainid = domainid FROM Domain WHERE domain = @domain
--print @domainid --Debug
begin
create table #hierarchy_levels (
item int,
parent integer,
domain varchar(80),
Domain_level integer)
declare
@level int
select
@level = 1
--Go into loop to grab the hierarchy of the perspective domainid that was derived from previous step.
insert #hierarchy_levels select domainid,domainparent,domain,@level from Domain Where
@domainid = domainid
While @@ROWCOUNT > 0
BEGIN
Select @level = @level + 1
insert #hierarchy_levels
select domainid,domainparent,domain,@level from Domain as new
where domainid in (select parent from #hierarchy_levels)
and not exists(select * from #hierarchy_levels as old where old.item =
new.domainid and ISNULL(old.parent, -1) = ISNULL(new.domainparent, -1))
END
update #hierarchy_levels
set domain_level = (domain_level-@level)*(-1)
END
--get chain for the perspective item passed in. Looking for the parent just below the root, which we do not want.
Select item from #hierarchy_levels where domain <> 'SUPPORT' AND parent = 1
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply