SQL duplicate rows subtraction in different tables MySQL

April 24, 2022

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.

>Solution :

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;