Post reply

Query hierarchical data

  • May 25, 2010 at 2:53 am

    #235536

    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

    #1174156

    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

    #1175020

    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

    #1175024

    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