Joining 3 tables with similar structure

Question

Hello all,

I'm executing sql queries in oracle 10g.

I want to join 3 tables into a single new table containing all the records from the 3 individual tables. The balance should be Summed up wherever the reg_id is duplicated between the 3 tables, such that there is just one entry per reg_id with the summed balance in my new table.

Sample data ( similar tables, with different values ) . tables : temp1, temp2, temp3

reg_id    |            balance
--------------------------------
92603013               183.77
92611902               419.46
92766121               77.04
93527720               24.84
93581368               120.09
93684684                89.88
93527720               113.66

Appreciate if someone can help me with the syntax.

Answer 1

Try the following...

INSERT INTO target_table (reg_id, balance)
SELECT reg_id, sum(balance) as balance
FROM (select reg_id, balance from temp1
      UNION ALL
      select reg_id, balance from temp2
      UNION ALL
      select reg_id, balance from temp3)
GROUP BY reg_id;

I haven't tried it so don't know if the syntax is correct and whether it'll will horribly mangle your data. :)

EDIT: Changed UNION to UNION ALL. Thanks, APC!

EDIT 2: Specified the columns explicitly per Tony's recommendation.

Answer 2

I'd suggest:

SELECT coalesce(t1.reg_id, t2.reg_id, t3.reg_id) AS the_reg_id,
       coalesce(t1.balance, 0.0) + 
       coalesce(t2.balance, 0.0) +
       coalesce(t3.balance, 0.0) AS the_balance
FROM t1 FULL OUTER JOIN t2 ON (t1.reg_id = t2.reg_id)
        FULL OUTER JOIN t3 ON (t1.reg_id = t3.reg_id)