From: hp275_removethis@skynet.be_removethis   
      
   Hi VC,   
      
   Thanks, your code worked allright; here's the real-world translation.   
      
   /* list all accounts where the count is not equal */   
   select a.voucher_no, a.ext_inv_ref, a.count_700500, b.count_400   
   from (select voucher_no, ext_inv_ref, count(*) count_700500   
   from agltransact   
   where client='MG'   
   and account=700500   
   and voucher_type in('P4','P5')   
   and voucher_no >= 63000020   
   and voucher_no <= 63000024   
   group by voucher_no, ext_inv_ref) a,   
   (select voucher_no, ext_inv_ref, count(*) count_400   
   from agltransact   
   where client='MG'   
   and account in('400400','400500','400301','400300')   
   and voucher_type in('P4','P5')   
   and voucher_no >= 63000020   
   and voucher_no <= 63000023   
   group by voucher_no, ext_inv_ref) b   
   where a.ext_inv_ref = b.ext_inv_ref   
   and a.voucher_no=b.voucher_no   
   and a.count_700500 != b.count_400;   
      
   Hrgds,   
   andy   
      
   In article ,   
    "VC"  wrote:   
      
   > Hello andy,   
   >   
   > Given:   
   >   
   > create table t1(ex_inv_ref int,   account int);   
   >   
   > the simplest way to do what you want is:   
   >   
   > select a.ex_inv_ref, count_6040, count_1512 from   
   >   (select ex_inv_ref, count(*) count_6040 from t1 where account=6040 group   
   > by ex_inv_ref) a,   
   >   (select ex_inv_ref, count(*) count_1512 from t1 where account=1512 group   
   > by ex_inv_ref) b   
   > where a.ex_inv_ref=b.ex_inv_ref and count_6040 != count_1512;   
   >   
   > Rgds.   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)