May 31, 2007 at 4:11 pm
I've got a scenario where I need to recurse through parent/child relationships in a table to get all records under a selected group, but I'm not sure how to do it. here's the scenario:
Table X
Group A
Group B
Group C
Agent1
Agent2
Agent3
Group D
Agent4
Agent5
Agent6
So if I select Group B, I need to recurse and get all Groups and Agents under and including Group B.
So this is a single table, with a GUID_ID and PARENT_GUID_ID. What is the method to recurse the table?
Any help is appreciated.
-R
June 1, 2007 at 10:22 am
Here is a simple solution based on information given. Doesn't work if more than one level deep.
--Build our test data
if object_id('tempdb..#temp_') is not null drop table #temp_
create table #Temp_ (guid_id INT,parent_guid_id int, text_ varchar(10))
insert into #temp_ (guid_id,parent_guid_id,text_)
select 1,0,'Group A' union all
select 2,0,'Group B' union all
select 3,0,'Group C' union all
select 4,3,'Agent 1' union all
select 5,3,'Agent 2' union all
select 6,3,'Agent 3' union all
select 7,0,'Group D' union all
select 8,7,'Agent 4' union all
select 9,7,'Agent 5' union all
select 10,7,'Agent 6' union all
select 11,10,'Sub-Agent'
--Solution
select *
from #temp_
where text_ = 'Group D'
union all
select b.*
from #temp_ a join #temp_ b on (a.guid_id = b.parent_guid_id)
where a.text_ = 'Group D'
June 1, 2007 at 11:48 am
This solution uses a Common Table Expression (CTE) and it will find all related no matter how deep, though I can't figure out how to make it work using the external "where" clause versus putting the criteria in the anchor query of the CTE (just not very satisfying, though it will work)
--Build our test data
if object_id('tempdb..#temp_') is not null drop table #temp_
create table #Temp_ (guid_id INT,parent_guid_id int, text_ varchar(10))
insert into #temp_ (guid_id,parent_guid_id,text_)
select 1,0,'Group A' union all
select 2,0,'Group B' union all
select 3,0,'Group C' union all
select 4,3,'Agent 1' union all
select 5,3,'Agent 2' union all
select 6,3,'Agent 3' union all
select 7,0,'Group D' union all
select 8,7,'Agent 4' union all
select 9,7,'Agent 5' union all
select 10,7,'Agent 6' union all
select 11,10,'Sub-Agent'
--Solution with CTE
with child_(guid_id, parent_guid_id, text_, level_) as
(select guid_id, parent_guid_id, text_, 0 as level_
from #temp_
where parent_guid_id = 0
and text_ = 'group d'
union all
select t.guid_id, t.parent_guid_id, t.text_, level_ + 1
from child_ c inner join #temp_ t on (t.parent_guid_id = c.guid_id)
)
select * from child_ order by guid_id
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply