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;

This page was last updated on May 01, 2006 04:28 PM.