samedi 25 avril 2015

SQL intersect with group by


Given these two tables/sets with different groups of items, how can I find which groups in set1 span across more than a single group in set2? how can I find the groups in set1 which cannot be covered by a single group in set2?

e.g. for tables below, A (1,2,5) is the only group that spans across s1(1,2,3) and s2(2,3,4,5). B and C are not the answers because both are covered in a single group s2.

I would prefer to use SQL (Sql Server 2008 R2 available).

Thanks.

set1                            set2
 +---------+----------+          +---------+----------+
 | group   |  item    |          | group   |  item    |
 `````````````````````+          `````````````````````+
 |   A     |    1     |          |   s1    |    1     |
 |   A     |    2     |          |   s1    |    2     |
 |   A     |    5     |          |   s1    |    3     |
 |   B     |    4     |          |   s2    |    2     |
 |   B     |    5     |          |   s2    |    3     |
 |   C     |    3     |          |   s2    |    4     |
 |   C     |    5     |          |   s2    |    5     |
 +---------+----------+          +---------+----------+

Use this sqlfiddle to try: http://ift.tt/1b1mvOi

Or use the script below to generate temp tables to try out the answers:

create table #set1 (grp varchar(5),item int)
create table #set2 (grp varchar(5),item int)

insert into #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 into #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 * from #set1
select * from #set2

--drop table #set1
--drop table #set2


Aucun commentaire:

Enregistrer un commentaire