Given these two tables/sets with different groups of items, how can I find which groups in how can I find the groups in set1 span across more than a single group in set2?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