May 25, 2010 at 2:53 am
hi,
i have a table storing the contact groups for my addressbook application.
groups can be nested and a contact may belongs to different groups.
i need a query that, given a groupid, returns all the contact present in that group and all subgroups.
The group table has these column :
GroupId int
ParentGroupId int
GroupName nvarchar(50)
I don't need a recursive query.
Any idea?
Thanks
May 28, 2010 at 3:14 pm
Given your table constraints, the only solution is to use a recursive query. If you had SQL Server 2008, there is a new data HEIRARCHICAL data type for this kind of situation. For the others, you can use a query like this (limiting to 5 levels of recursion):
WITH cte AS
(
SELECT GroupId, ParentGroupId, GroupName
FROM #Test
WHERE GroupId = @GroupID
UNION ALL
SELECT t1.GroupId, t1.ParentGroupId, t1.GroupName
FROM #Test t1
JOIN cte t2 ON t1.ParentGroupId = t2.groupID
)
SELECT * FROM cte OPTION (MAXRECURSION 5)
without more data I couldn't do much more for you.
June 1, 2010 at 3:06 pm
Start here
You can use nested sets to simplify any hierarchy. Kamfonas' site should get you going -- the first thing to do is to change your parent/child model to a brand new table. It'll take some doing, but you'll get to
SELECT * from hier dsc INNER JOIN hier anc ON dsc.LFT BETWEEN anc.LFT and anc.RGT
WHERE anc.tag = 'requested hierarchy root'
It's like magic.
June 1, 2010 at 3:13 pm
Yeah that's the solution we're using for a heirarchy data structure as well. Works pretty well, although it requires you to do some pretty hefty data re-organization.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply