CODEWITHSUNDEEP
sqlt-sql
Alex.Ritna
Alex.Ritna

Reputation: 1984

T-SQL Child/Parent query

I have 4 tables, group, membership, person and attribute.

Group

GroupID
GroupName
ParentGroupID

Membership

PersonID
GroupID

Person

PersonID
Name

Attribute

AttributeID
GroupID
Value

Attributes are assigned to groups, groups have people assigned via the membership table.

I would like to display all the attributes relating to a particular person.

I am having no troubles doing this for someone in a group with no parent, but some of the groups have nesting 2 or 3 levels deep. My attempts to use the CTE approach haven't yielded any results thus far.

Example Result

Person.Name Membership.GroupID  Group.GroupID  Group.ParentGroupID  Attribute.Value
Fred        3                   1              NULL                 'Attribute for Top level group'
Fred        3                   2              1                    'Attribute for a sub group'
Fred        3                   3              2                    'Attribute for third sub group'
Fred        5                   4              1                    'Attribute for Top level group - this is a duplicate?'
Fred        5                   5              4                    'Attribute for second sub group'

Hope that is clear enough. Fred is a member of Group 3, whichhas a parent group of 2, which in turn has a parent group of 3 - then display each of attributes that match for each group (inner join on the Group.ID against Attribute.GroupID would achieve this)

edit - Just to add a note, each Person can be a member of multiple groups.

Upvotes: 0

Views: 496

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

DDL + DML

create table groups (groupid int, groupname varchar(10), parentgroupid int)
insert groups select 1, 'A', null;
insert groups select 2, 'B', null;
insert groups select 3, 'C', 1;
insert groups select 4, 'D', null;
insert groups select 5, 'E', null;
create table membership (personid int, groupid int);
insert membership select 1, 3;
insert membership select 1, 4;
create table person (personid int, name varchar(100));
insert person select 1, 'Jim';
create table Attribute(AttributeID int, GroupID int, Value varchar(100));
insert Attribute select 1, 1, 'At1-1';
insert Attribute select 2, 1, 'At1-2';
insert Attribute select 3, 2, 'At2-1';
insert Attribute select 4, 3, 'At3-1';
insert Attribute select 5, 4, 'At4-1';

The QUERY

;with tmp as (
    select groupid membership_groupid, groupid
    from membership
    where personid = 1
    union all
    select membership_groupid, g.parentgroupid
    from groups g
    join tmp on tmp.groupid = g.groupid
)
select p.name, membership_groupid, g.groupid, g.parentgroupid, a.value
from person p
join tmp t on 1=1
join groups g on g.groupid = t.groupid
join attribute a on a.groupid = t.groupid
where p.personid = 1

And if it's possible for a person to be a member of both GROUP A and GROUP A's parent, you will need to break the tie to show the attribute only ONCE.

;with tmp as (
    select groupid membership_groupid, groupid
    from membership
    where personid = 1
    union all
    select membership_groupid, g.parentgroupid
    from groups g
    join tmp on tmp.groupid = g.groupid
)
select p.name, membership_groupid, g.groupid, g.parentgroupid, a.value
from person p
join (select *, rn=row_number() over (partition by groupid
                                      order by case when membership_groupid=groupid then 1
                                                    else 2 end,
                                               membership_groupid)
      from tmp) t on t.rn=1
join groups g on g.groupid = t.groupid
join attribute a on a.groupid = t.groupid
where p.personid = 1

Upvotes: 2

Related Questions