CODEWITHSUNDEEP
asp.nett-sqldatabase-designnested
mko
mko

Reputation: 7325

Parent/child relations in sql

I am trying to define a sql table for groups and subgroups, and so far my design is as follows

groupID groupname       parentgroupID
1       group1          null
2       group2          null
3       subgroup1.1     1
4       subgroup1.1.1   3

I think I am on the right track. Now I would like to retreive data for subgroup 1.1.1 in order to be able to display it in a bussiness tier

group1 > subgroup 1.1 > subgroup 1.1.1

Should I do this in tsql, or I am suppose to do a loop in asp.net in order to get all available parent groups?

This question is both sql and asp.net question so I will post it in both categories.

Thanks for participating

Upvotes: 1

Views: 371

Answers (1)

marc_s
marc_s

Reputation: 754418

You can solve this - provided your on SQL Server 2005 or newer with a recursive CTE:

-- define a CTE (Common Table Expression) with a name
;WITH groups AS
(
    -- define the "anchor" - those top-level rows
    SELECT 
        GroupID, GroupName, 
        CAST(NULL AS INT) AS 'ParentGroupID', 1 AS 'Level',
        CAST('# ' + GroupName AS VARCHAR(1000)) AS 'Breadcrumb'
    FROM @test
    WHERE ParentGroupID IS NULL

    -- recursive join - joins "child" rows to "parent" rows already in the result set       
    UNION ALL

    SELECT 
       t2.GroupID, t2.GroupName, t2.ParentGroupID, g.Level + 1 AS 'Level',
       CAST(g.Breadcrumb + ' > ' + t2.GroupName AS VARCHAR(1000))
    FROM @test t2
    INNER JOIN groups g ON t2.ParentGroupID = g.GroupID  -- join back to the CTE
)
SELECT * 
FROM groups

This should give you the results something like this:

GroupID  GroupName        ParentGroupID  Level  Breadcrumb
  1       group1             NULL          1     # group1
  2       group2             NULL          1     # group2
  3       subgroup1.1         1            2     # group1 > subgroup1.1
  4       subgroup1.1.1       3            3     # group1 > subgroup1.1 > subgroup1.1.1

Any additional formatting should be done in your front-end (web, client/server).

The Level column is an extra bonus, telling you what level in the hierarchy each row is positioned on.

Upvotes: 3

Related Questions