Binaryworld - Search for combination of column values in different rows. ... [ SQL Server -> T-SQL ]

One solution for searching for combination of column values in different rows. Please see the script for more details & example.

Click here to copy the following block

create table #Tbl1
(
number int primary key,
name varchar (25)
);

create table #Tbl2
(
number int,
field1 varchar (5),
field2 varchar (5)
);
go
insert into #Tbl1 values ( 1 , 'Value1' );
insert into #Tbl1 values ( 2 , 'Value2' );
go
insert into #Tbl2 values ( 1 , 'from', 'me');
insert into #Tbl2 values ( 1 , 'from', 'me1');
insert into #Tbl2 values ( 1 , 'from1', 'me');
insert into #Tbl2 values ( 1 , 'to', 'you');
insert into #Tbl2 values ( 1 , 'to', 'me');
go
insert into #Tbl2 values ( 2 , 'to', 'you');
insert into #Tbl2 values ( 2 , 'to', 'you');
insert into #Tbl2 values ( 2 , 'to1', 'you');
insert into #Tbl2 values ( 2 , 'to', 'you1');
go
select* from #Tbl1;
/*
number   name
----------- -------------------------
     1 Value1
     2 Value2
*/
select* from #Tbl2;
/*
number   field1 field2
----------- ------ ------
     1 from me
     1 from me1
     1 from1 me
     1 to   you
     1 to   me
     2 to   you
     2 to   you
     2 to1  you
     2 to   you1
*/
go
select g1.Number , g1.name
from #Tbl1 as g1
join #Tbl2 as g2
on (g1.number = g2.number)
where ( g2.field1 = 'from' And g2.field2 = 'me' ) Or
    ( g2.field1 = 'to' And g2.field2 = 'you' )
group by g1.Number , g1.name
having sum( case
        when ( g2.field1 = 'from' And g2.field2 = 'me' ) then -1
        when ( g2.field1 = 'to' And g2.field2 = 'you' ) then 1
    end ) = 0;
go
drop table #Tbl1;
drop table #Tbl2;