MySQL two (almost) identical table merge the values and default unique values-CodePudding

I have two almost identical MySQL tables besides a date column, and I would like to merge them with a select query to create a final table displaying both dates.

If there is a unique acc_id in one table -> i want to default the amount in the other to 0.

Here is an example of what i mean:

Table1:

id	acc_id	name	aug_amount
1	123	name1	100
2	456	name2	200
3	333	name3	300

Table2:

id	acc_id	name	sep_amount
1	123	name1	200
2	456	name2	300
3	444	name4	400

Merged table:

acc_id	name	aug_amount	sep_amount
123	name1	100	200
456	name2	200	300
333	name3	300	0
444	name4	0	400

Is there a way i can achieve this with a singular SQL query? I've been playing around for a while but i cant seem to crack it.

Help appreciated! Thanks for reading.

CodePudding user response：

Ignoring the PK column, here is one solution where we union the two tables and then select from it:

select acc_id, name, sum(aug_amount) as aug_amount, sum(sep_amount) as sep_amount
from (
  select acc_id, name, aug_amount, 0 as sep_amount
  from table1
  union
  select acc_id, name, 0, sep_amount
  from table2
)z
group by acc_id, name
order by name;

acc_id	name	aug_amount	sep_amount
123	name1	100	200
456	name2	200	300
333	name3	300	0
444	name4	0	400

View on DB Fiddle