Select unique combination of child occurrences for a parent

164 views Asked by At

Added SQLFiddle: http://sqlfiddle.com/#!3/0792f/6/0

Given the following table structure: Relationships (id_object_parent, id_object_child)

Let's assume the following basic data:

1,10
1,11
2,10
2,11
3,10
3,11
3,12
3,13
4,10
4,13

Based on this data, my conclusions should be:

- 10 has 4 occurrences (parent 1+2+3+4)
- 11 has 3 occurrences (parent 1+2+3)
- 12 has 1 occurrence (parent 3)
- 13 has 2 occurrences (parent 3+4)
- 10+11 has 3 occurrences (parent 1+2+3)
- 10+13 has 2 occurrences (parent 3+4)
- 10+11+12 has 1 occurrence (parent 3)
- 10+11+12+13 has 1 occurrence (parent 3)

In short: I want to get all unique combinations of childs and their occurrence count to the same parent.

My first setup has been to do a cross apply on a stuff:

SELECT
ca1.childs entitlements,
ca1.child_count,
COUNT(r.id_object_parent) same_parent_count
FROM Relationships r
CROSS APPLY (SELECT (SELECT COUNT(rg.id_object_child)
    FROM Relationships rg
    WHERE rg.id_object_parent = r.id_object_parent), STUFF((SELECT ',' + CONVERT(varchar,rg.id_object_child)
    FROM Relationships rg
    WHERE rg.id_object_parent = r.id_object_parent
    GROUP BY rg.id_object_child
    FOR XML PATH('')), 1, 1, '')) ca1 (child_count, childs)
GROUP BY ca1.childs, ca1.child_count

The problem with this query is that I get all the unique "patterns", but also only the "complete" ones. The end result is that this query will not process parent #3 correctly, as childs 10+11 are also sub-occurrences of parent 3, but this query will ignore that.

Any suggestions?

0

There are 0 answers