sql server - SQL intersect with group by -

given these 2 tables/sets different groups of items, how can find groups in set1 span across more single group in set2? how can find groups in set1 cannot covered single group in set2?

e.g. tables below, a (1,2,5) group spans across s1(1,2,3) , s2(2,3,4,5). b , c not answers because both covered in single group s2.

i prefer use sql (sql server 2008 r2 available).

thanks.

set1                            set2  +---------+----------+          +---------+----------+  | group   |  item    |          | group   |  item    |  `````````````````````+          `````````````````````+  |       |    1     |          |   s1    |    1     |  |       |    2     |          |   s1    |    2     |  |       |    5     |          |   s1    |    3     |  |   b     |    4     |          |   s2    |    2     |  |   b     |    5     |          |   s2    |    3     |  |   c     |    3     |          |   s2    |    4     |  |   c     |    5     |          |   s2    |    5     |  +---------+----------+          +---------+----------+ 

use sqlfiddle try: http://sqlfiddle.com/#!6/fac8a/3

or use script below generate temp tables try out answers:

create table #set1 (grp varchar(5),item int) create table #set2 (grp varchar(5),item int)  insert #set1 select 'a',1 union select 'a',2 union select 'a',5 union select 'b',4 union select 'b',5 union select 'c',3 union select 'c',5 insert #set2 select 's1',1 union select 's1',2 union select 's1',3 union select 's2',2 union select 's2',3 union select 's2',4 union select 's2',5  select * #set1 select * #set2  --drop table #set1 --drop table #set2 

select groups set1 there no groups in set2 items in set1 exists in set2:

select s1.grp set1 s1 not exists(   select * set2 s2 not exists(     select item set1 s11      s11.grp = s1.grp      except      select item set2 s22     s22.grp = s2.grp)) group s1.grp