SQL find sets with shared members (relational division)

Question

I have separate sets of "classes" and "groups", each of which is assigned one or more tags. I would like to find for each group a subset of classes that contains the same (or more) tags for each group.

Some sample data:

declare @Groups table ( GroupID int, TagID int ) insert @Groups values (1,1),(1,2),(1,3), (2,1),(2,2), (3,1),(3,2),(3,3),(3,4) declare @Classes table ( ClassID int, TagID int ) insert @Classes values (1,1),(1,2), (2,1),(2,2), (3,1),(3,2),(3,3) select * from @Groups select * from @Classes 

And the conclusion:

 GroupID TagID 1 1 1 2 1 3 2 1 2 2 3 1 3 2 3 3 3 4 ClassID TagID 1 1 1 2 2 1 2 2 3 1 3 2 3 3 

An example set of results would look like this:

 declare @Results table ( GroupID int, ClassID int ) insert @Results values (1,3),(2,1),(2,2),(2,3),(3,null) select * from @Results 

Output result:

 GroupID ClassID 1 3 2 1 2 2 2 3 3 NULL 

I understand that this is a type of relational division problem, including having and count . These posts describe what I want to do, but I cannot figure out how to apply the examples to the specific case above:

• http://weblogs.sqlteam.com/peterl/archive/2010/07/02/Proper-Relational-Division-With-Sets.aspx
• SQL Join only if all records have a match.
• How can I structure the query to provide me with only rows that match all the values ​​in the list of CSV identifiers in T-SQL .
• SQL: Selecting Sets Containing Exactly Specified Elements
• Sql query to search for users with common tags in a specific set

Answer 1

I think this should also work.

 select distinct g.GroupID, c.ClassID from @Groups g left join @Classes c on g.TagID = c.TagID where not exists ( select * from @Groups g2 where g2.GroupID = g.GroupID and g2.TagID not in ( select TagID from @Classes c2 where c2.ClassID = c.ClassID ) ) or c.ClassID is null 

Answer 2

You can join the tables together and require that all tags from the group be in the class:

 select g.GroupID , c.ClassID from @Groups g join @Classes c on c.TagID = g.TagID group by g.GroupID , c.ClassID having count(c.TagID) = ( select count(*) from @Groups g2 where g2.GroupID = g.GroupID ) 

This does not display groups without an appropriate class, and I cannot think of an easy way to do this.

Example in SQL Fiddle.