Compare two select statements with fields in two tables

I have 3 tabl开发者_运维百科es, one table called TRANSACTIONS has the from_account_id, the other table called PAYMENTS has the to_account_id and the third table called ACCOUNTS has all the data (from and to accounts numbers, from_account_id, to_account_id) but also the account_owner_code which are the account owners. An account owner may have more than one account number in the ACCOUNTS table.

Now I wish to find transactions that where made between accounts of different owners, so to be exact I want to find transactions that were made from account owner A.acount_1234 to accountOwnerB.acount56789 and not the transactions between accounts of the same owner.

The problem is I am not sure how to compare the two as I am using subselects. Can anyone help?

---

Transcription of information from comment:

• TRANSACTION_TABLE: ID, INPUT_AMOUNT, FROM ACCOUNT_ID, DESCRIPTION
• PAYMENTS_TABLE: ID, TO_AMOUNT, TO_ACCOUNT_NUMBER, COMMISSION, POST_DATE, etc
• ACCOUNTS_TABLE: ID, ACCOUNT_NUMBER, BALANCE, OPEN_DATE, OWNER_CODE

---

Assumptions: ID value in the Transactions table matches up with the ID value in the Payments table for each pair. Otherwise, I am not sure how you are going to guarantee that you are dealing with both sides of the same event.
Each transaction record has exactly one payment record
All transactions and payments have matching account records.

Condition: I hate sub-selects, kill them if Ican.

Select ~whatever
From Transaction_Table t
  Inner Join Accounts_Table at on t.From_Account_ID=at.Account_ID
  Inner Join Payments_Table p on t.ID=p.ID
  Inner Join Accounts_Table ap on p.To_Account_ID=ap.Account_ID
Where at.Owner_Code != ap.Owner_Code