Add amounts from two different tables Oracle

Question

I have two tables, an interest table and a charges table. The Interest Table Has debit Interest and credit Interest Which I have been able to get the difference using this query:

select e.sol_id, (sum(CASE WHEN e.INTEREST_IND = 'D' THEN e.AMOUNT_IN_LCY ELSE 0 END)-
sum(CASE WHEN e.INTEREST_IND = 'C' THEN e.AMOUNT_IN_LCY ELSE 0 END)) as Difference 
from tbaadm.INTEREST_DETAILS e
group by e.sol_id;

My Output is correct:

I now need to add the amount in the Charges table which I have attempted like this:

select e.sol_id, (sum(CASE WHEN e.INTEREST_IND = 'D' THEN e.AMOUNT_IN_LCY ELSE 0 END)-
sum(CASE WHEN e.INTEREST_IND = 'C' THEN e.AMOUNT_IN_LCY ELSE 0 END) + 
sum(f.AMOUNT_IN_LCY)) as Difference 
from tbaadm.INTEREST_DETAILS e,TBAADM.CHARGE_DETAILS f
where F.SOL_ID = E.SOL_ID
group by e.sol_id, f.sol_id

This Query after hanging for a moment gives me this Output:

Which is wrong Considering that while Subtracting Debits from the credit in the first tables has entries for Branch 000,001 and 003, How can I add the Amount from the second table and maintain my result set even if the second table does not have entries for branch 000 and 003?

Answer 1

I think you need an OUTER JOIN.

You essentially have two Datasets like this:

Set1: ID VAL     Set2: ID Val    => Expected Result:
       1  10            1   5       1  15
       2  20                        2  20
       3  30                        3  30

So you need a RIGHT OUTER JOIN

For your SQL it would be:

select  e.sol_id
       ,(sum(CASE WHEN e.INTEREST_IND = 'D' THEN e.AMOUNT_IN_LCY ELSE 0 END)
          - sum(CASE WHEN e.INTEREST_IND = 'C' THEN e.AMOUNT_IN_LCY ELSE 0 END)
          + sum(f.AMOUNT_IN_LCY)
        ) as Difference 
  from  tbaadm.INTEREST_DETAILS e
       ,tbaadm.CHARGE_DETAILS   f
 where  E.SOL_ID = F.SOL_ID (+)
 group  by e.sol_id, f.sol_id;

The (+) denotes the Table, which can also be NULL. Another way to write it would be:

select  e.sol_id
       ,(sum(CASE WHEN e.INTEREST_IND = 'D' THEN e.AMOUNT_IN_LCY ELSE 0 END)
          - sum(CASE WHEN e.INTEREST_IND = 'C' THEN e.AMOUNT_IN_LCY ELSE 0 END)
          + sum(f.AMOUNT_IN_LCY)
        ) as Difference 
  from  tbaadm.INTEREST_DETAILS e
        LEFT OUTER JOIN
          tbaadm.CHARGE_DETAILS f
        ON E.SOL_ID = F.SOL_ID
 group  by e.sol_id, f.sol_id;

The Second Version is conforming to the SQL Standard, whereas the first one (+) is only supported by Oracle.