By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Recursive CTE retain First Child

P: 3
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
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
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.