Join and unpivot on multiple tables

Question

I have two tables:

Correction table:

C_CORRECTIONS
CorrAmount  |HalfYear1  |HalfYear2  |Type   |REFNUM |
-----------------------------------------------------
1000        |50         |50         |HYINT  |R123   |

Collected Table:

P_COLLECTED
Amount      |Type   |Account    |REFNUM |
-----------------------------------------
1500        |TOTAL  |ACCT1      |R123   |
50          |HYINT  |ACCT2      |R123   |
50          |HYINT  |ACCT3      |R123   |

What I need is:

CorrAmount  |Type   |Account    |REFNUM |
-----------------------------------------
1000        |TOTAL  |ACCT1      |R123   |
50          |HYINT  |ACCT2      |R123   |
50          |HYINT  |ACCT3      |R123   |

I have tried below:

SELECT DISTINCT C.AMT, P.ACCOUNT,C.REFNUM FROM (
SELECT U.AMT, U.Type, REFNUM
FROM C_CORRECTIONS
UNPIVOT
(
  AMT
  FOR INT_AMT IN (CorrAmount,HalfYear1,HalfYear2)
) U
) C
LEFT JOIN P_COLLECTED P
ON C.REFNUM = P.REFNUM AND C.AMT = P.Amount;

Result:

AMT     |ACCOUNT    |REFNUM |
--------|-----------|-------|
50      |ACCT2      |R123   |
50      |ACCT3      |R123   |
1000    |           |R123   |

Edit: Okay. So i have used left join instead of join, and put in distinct and i get main amount, but i cannot seem to get the account for it yet.

Answer 1

You unpivot C_CORRECTIONS and then join P_COLLECTED to it.

But perhaps you could start from P_COLLECTED and join C_CORRECTIONS to it.
Since P_COLLECTED already has the multiple rows.

Test example:

declare @P_COLLECTED table (Amount int, Type varchar(8), Account varchar(8), REFNUM varchar(8));
declare @C_CORRECTIONS table (CorrAmount int, HalfYear1 int, HalfYear2 int, Type varchar(8), REFNUM varchar(8));

insert into @P_COLLECTED (Amount, Type, Account, REFNUM) values
(1500,'TOTAL','ACCT1','R123'),
(50  ,'HYINT','ACCT2','R123'),
(50  ,'HYINT','ACCT3','R123');

insert into @C_CORRECTIONS (CorrAmount, HalfYear1, HalfYear2, Type, REFNUM) values
(1000,24,26,'HYINT','R123');

select 
(case when t.Type = 'TOTAL' then c.CorrAmount else t.Amount end) as Amount,
t.Type, t.Account, t.REFNUM
-- , c.*
from @P_COLLECTED t
left join @C_CORRECTIONS c 
      -- on (t.REFNUM = c.REFNUM and t.Type in ('TOTAL',c.Type));
      on (t.REFNUM = c.REFNUM and t.Type = 'TOTAL');