Simple Select with exists

[eramgarden]

Been working on this since this morning :

 

Table cProcessComp has:

oldDebtCode:A000000028 NewDebtCode:L000000789

 

Table BKRRPTPF has this :

BKDCOD: A000000028 BKAgency:100

BKDCOD: L000000789 BKAgency:009

 

Now, this SQL should not pick up that row since the BKagencies are not equal but it does pick it up!

 

What am I doing wrong??

select  BKDCOD,NewDebtCode,OldDebtCode,BKAGENCY 
   From BKRRPTPF 
               Inner Join cProcessComp 
                   on  BKDCOD= OldDebtCode   
where   exists ( select * from BKRRPTPF b1 
        inner  join BKRRPTPF b2 
       on b1.BKAgency = b2.BKAgency 
        inner join cProcessComp 
          on b1.bkdcod=newdebtcode and b2.bkdcod=olddebtcode) 

 

I get this row back:

A000000028 L000000789 A000000028 100

[Gill Bates]

I ran this query using your data and I do not get any rows back. You most likely have other rows in your tables, along with the ones you mentioned.