How To Merge Two Table into one output data

Question

I have two tables with similar information:
1st Table For Estimation Data :

EstChargeCode | EstAmount
------------- | -------------
CNFS0001      | 43,250,000.00
CNIH0001      | 0.00
CNIH0001      | 2,625,000.00
CNIP0001      | 4,500,000.00
CNIP0005      | 2,250,000.00
CNOH0001      | 20,484,690.00
CNOP0001      | 0.00

2nd Table for Actual Data :

ActChargeCode | ActAmount
------------- | -------------
CNFS0001      | 39,950,000.00
CNIH0001      | 1,300,000.00
CNIH0001      | 950,000.00
CNIH0001      | -950,000.00
CNIH0001      | 950,000.00
CNIP0001      | 4,500,000.00
CNIP0005      | 2,250,005.00
CNOH0001      | 20,484,690.00
CNOP0001      | 3,300,000.00

if using Union All to merge the table from above then the result like this

ChargeCode | EstAmount     | ActAmount
---------- | ------------- | -------------
CNFS0001   | 43,250,000.00 | -------------  
CNIH0001   | 0.00          | -------------
CNIH0001   | 2,625,000.00  | -------------  
CNIP0001   | 4,500,000.00  | -------------
CNIP0005   | 2,250,000.00  | -------------
CNOH0001   | 20,484,690.00 | -------------
CNOP0001   | 0.00          | -------------
CNFS0001   | ------------- | 39,950,000.00
CNIH0001   | ------------- | 1,300,000.00
CNIH0001   | ------------- | 950,000.00
CNIH0001   | ------------- | -950,000.00
CNIH0001   | ------------- | 950,000.00
CNIP0001   | ------------- | 4,500,000.00
CNIP0005   | ------------- | 2,250,005.00
CNOH0001   | ------------- | 20,484,690.00
CNOP0001   | ------------- | 3,300,000.00

I need to group both data into single result data like this

ChargeCode | EstAmount     | ActAmount
---------- | ------------- | -------------
CNFS0001   | 43,250,000.00 | 39,950,000.00
CNIH0001   | 0.00          | 1,300,000.00
CNIH0001   | 0.00          | 950,000.00
CNIH0001   | 0.00          | -950,000.00
CNIH0001   | 0.00          | 950,000.00
CNIH0001   | 2,625,000.00  | 0.00
CNIP0001   | 4,500,000.00  | 4,500,000.00
CNIP0005   | 2,250,000.00  | 2,250,005.00
CNOH0001   | 20,484,690.00 | 20,484,690.00
CNOP0001   | 0.00          | 3,300,000.00

I don't know how handle this out. Any help would be greatly appreciated!

You need just to join your tables with `INNER JOIN` – Ilyes Sep 08 '17 at 10:44 — Ilyes, Sep 08 '17 at 10:44

score 1 · Answer 1 · answered Sep 08 '17 at 09:57

you can use code like below

  select isnull(EstChargeCode ,ActChargeCode ) as ChargeCode ,isnull(EstAmount,0) as 
    EstAmount , isnull(ActAmount,0) as ActAmount from Estimation full join Actual 
    on Estimation.EstChargeCode =Actual.ActChargeCode

score 0 · Answer 2 · edited Sep 08 '17 at 10:02

0

select a.EstChargeCode as ChargeCode , 
       a.EstAmount, 
       b.ActAmount 
from Estimation_Data as a 
LEFT JOIN Actual_Data as b 
ON a.ActChargeCode =b.ActChargeCode

edited Sep 08 '17 at 10:02

Ankit Bajpai

13,128
4
25
40

answered Sep 08 '17 at 09:56

The beginner

624
4
17

@ankit what is wrong in my answer?check your answer there are no such a ActChargeCode column in Estimation_Data table – The beginner Sep 08 '17 at 10:07
Really? `LEFT JOIN`? – Ilyes Sep 08 '17 at 10:34
1

@Sami Could be valid - we have no idea whether rows are guaranteed to exist in either table. Estimates could have been made with no actual amounts yet. Actual amounts could be known without an estimate - OP didn't specify.. Without guesswork the only valid option would be full outer join (like Hassan's answer). – Bridge Sep 08 '17 at 11:58
1

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Sep 08 '17 at 15:23
Code only answers are low quality and subject to deletion, there should always be at least one or two sentences (optimally a paragraph) about what question you are addressing, conditions, pitfalls, etc). – Eric Leschinski Sep 09 '17 at 00:28

score 0 · Answer 3 · edited Sep 08 '17 at 11:21

0

You can use JOIN to achieve the result:

SELECT t2.ActAmount  ChargeCode ,nvl(t1.EstAmount,0.0) EstAmount, nvl(t2.ActAmount,0.0) ActAmount
FROM <Estimation Data table> t1
RIGHT OUTER JOIN <Actual Data> t2 ON t1.EstChargeCode = t2.ActChargeCode ;

For more clarification, there is an explanation here: What is the difference between "INNER JOIN" and "OUTER JOIN"?

edited Sep 08 '17 at 11:21

Syntax Error

1,600
1
26
60

answered Sep 08 '17 at 10:03

Nikita_dandwani

1

1

Use JOIN instead of Union – The beginner Sep 08 '17 at 10:09

score 0 · Answer 4 · answered Sep 08 '17 at 10:43

0

You use INNER JOIN like:

SELECT T1.EstChargeCode AS EstChargeCode,
       T1.EstAmount AS EstAmount1,
       T2.EstAmount AS EstAmount2
FROM Table1 T1 INNER JOIN Table2 T2 ON T1.EstChargeCode = T2.ActChargeCode;

answered Sep 08 '17 at 10:43

Ilyes

14,640
4
29
55

How To Merge Two Table into one output data

4 Answers4