Select subtraction in columns of different tables

0

I've been trying to perform a select that subtracts one column from one table to another column from another table. Look at the image below what I need:

Therefore, I need to add the quantidade column of the lista_geral table, while the values of the cod_material and local column are the same. Then subtract the result obtained by the values of the cautela table, that is, as long as the values of the cod_material and local columns, of the cautela table, are the same as the lista_geral table.

The best result I got was with the select below:

SELECT cod_material, (SUM(quantidade) - (SELECT SUM(quantidade) 
FROM cautela WHERE local = 'xxx' GROUP BY cod_material)) as total_material 
FROM lista_geral WHERE local = 'xxx' GROUP BY cod_material;

However, I did not get the result I need, since I'm subtracting it without restricting the columns cod_material and local .

    
asked by anonymous 18.10.2017 / 15:38

1 answer

1

Try to do with inner join as the example below:

  SELECT c.cod_material, SUM(c.quantidade) - SUM(lg.quantidade) 
  FROM cautela c
  INNER JOIN lista_geral lg
  ON (c.cod_material = lg.cod_material and c.local = lg.local)
  group by c.cod_material;
    
18.10.2017 / 19:29