MySQL merge two tables and get sum-CodePudding

i have a tableA:

 ------ -------- ------- 
| name | code   | num   |
 ------ -------- ------- 
| A    | no1    | 300   |
| A    | no2    | 100   |
 ------ -------- -------

i also have a tableB:

 ------ -------- ------- 
| name | code   | num   |
 ------ -------- ------- 
| A    | no1    | -100  |
| A    | no5    | 77    |
| B    | no7    | 2     |
 ------ -------- -------

My goal is want to get tableC like this:

 ------ -------- ------- 
| name | code   | num   |
 ------ -------- ------- 
| A    | no1    | 200   |
| A    | no2    | 100   |
| A    | no5    | 77    |
| B    | no7    | 2     |
 ------ -------- -------

i used union and join,but the result is not right,please tell me how to get TableC?

CodePudding user response：

So, instead of JOIN what you need is UNION. You can use "UNION ALL" or "UNION", it depends if you want the duplicated rows or not.

In any case, after the UNION, group that result into a subquery to get the SUM()

SELECT
u.name,
u.code,
SUM(u.num),
FROM
(
SELECT name, code, num FROM tableA
UNION ALL
SELECT name, code, num FROM tableB
) u
GROUP BY u.name, u.code