SQL duplicate rows subtraction in different tables MySQL-CodePudding

I have a problem with subtraction of sums of values from different tables. That's what I mean. First table:

id_p | num
-----------
1    | 5
2    | 3

Second table:

id_p | num
-----------
1    | 2
1    | 2
2    | 1

And the result must be:

id_p | res
-----------
1    | 1
2    | 2

So for id_p = 1 it must be 5 - 2 - 2 = 1, and for id_p = 2 3 - 1 = 2. But I receive such result:

id_p | res
-----------
1    | 6
2    | 2

It dublicates 5 in inner, left, right, cross joins, so it's always gives me (5 5) - (2 2), not a 5 - (2 2). Please, help me to understand how to do this in MySQL. Also it must work in both ways.

UPD: there might be another columns, so they also can affect on the duplicating.

CodePudding user response：

One approach joins to a subquery which finds ths sums:

SELECT t1.id_p, t1.num - COALESCE(t2.num, 0) AS res
FROM table1 t1
LEFT JOIN
(
    SELECT id_p, SUM(num) AS num
    FROM table2
    GROUP BY id_p
) t2
    ON t2.id_p = t1.id_p
ORDER BY t1.id_p;

CodePudding user response：

Another approach might be using a left join with ids

SELECT f.`id`, f.`num`-SUM(s.`num`) FROM `first_table` f 
LEFT JOIN `second_table` s ON s.`id`=f.`id` GROUP BY f.`id`

CodePudding user response：

One more solution using UNION ALL

select id_p, sum(num) as sum_num from ( -- calculate sum
  -- union 2 tables 
  select * from t1
  union all
  select id_p, -num from t2
) t group by id_p; -- group by id_p

MySQL online sandbox