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?