468,242 Members | 1,487 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Recursive CTE retain First Child

Hello i am trying to traverse a link table called..groupsgroups

what i need is get the the first found parent id for each subsequent recursion.

effectively the groupid from
the first query listed in the subseqent records

so from

Groupid, parentGroupID
2 1
3 2
4 2
5 4


so input = groupid 1

Groupid, parentGroupID, firstParentID
2 1 2
3 1 3
4 2 2
5 4 2

the intention is to create a result set i can count the number of recursive groups
under each of the immediate child results.

can it be done...

With GroupChildren (GroupID, ParentGroupId, [Level])
AS
(
Select GroupID, ParentGroupId, 0 as [Level] from GroupsGroups AS e where ParentGroupID = @groupid
UNION ALL
SELECT e.GroupID, e.ParentGroupId, [Level] + 1 from GroupsGroups as e inner join groupchildren as d on e.ParentGroupID = d.GroupID
)

select GroupID, ParentGroupID from GroupChildren
Oct 15 '08 #1
1 1450
ck9663
2,878 Expert 2GB
You lost me somewhere.

Could you give a little mode detail explanation on your sample data and your desired output? I did not see any GroupID = 1 so I'm kind'a lost.

-- CK
Oct 16 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

19 posts views Thread by Carlos Ribeiro | last post: by
8 posts views Thread by Ryan Stewart | last post: by
25 posts views Thread by Mike MacSween | last post: by
7 posts views Thread by Michael | last post: by
4 posts views Thread by Mike | last post: by
9 posts views Thread by pereges | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.