This is a puzzling SQL task. I'd like to do it with a query instead of stepping through with a cursor and doing it the "hard way".
If I have two tables TableA and TableB each with a grouping column as below:
id group id group
------ ------ ------ ------
1 D 1 X
2 D 2 X
3 D 3 Y
4 D 4 Y
4 E 5 Y
5 E 5 Z
5 F 6 Z
Note the group names are not the same name.
I want to know if a given TableB group is comprised entirely of IDs which are also grouped together in any group in TableA. The TableA group can have more IDs than the TableB group, so long as it has all of the same IDs as the TableB group. IDs can be in more than one group in either table.
From the tables above, I should find out that group X from TableB matches a group in TableA, but groups Y and Z do not.
I've tried many different queries, subqueries, recursive CTEs. I've only ended up with wrong results and headaches. The real dataset is significantly larger, so performance should be considered a factor too. Unfortunately, that means the cross-join solution proposed in an answer below won't work.
Is this even possible with SQL?