Select subtraction in columns of different tables

Asked

Viewed 436 times

0

I have been trying to perform a select that subtracts a column in a table, by another column in another table. See in the image below what I need:

inserir a descrição da imagem aqui

Therefore, I need to add the column quantidade table lista_geral, while the values in the column cod_material and local are equal. Then subtract the result obtained from the table values cautela, that is, while column values cod_material and local, table cautela, are equal to the table lista_geral.

The best result I got was with 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, as this subtracting without restricting the columns cod_material and local.

php mysql sql

  • Can you provide the sql of the tables as well as the sample data mass? So I can try something here in my environment.

  • Two subselects aggregating the tables by cod_material , use these selects as virtual tables making a Join on them , just operate the difference.

  • You say you want to add by cod_material and localbut does GROUP BY just by cod_material. Tnete do GROUP BY cod_material, local.

2 answers

0

Try doing 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;

  • The query runs, but the wrong result. :/

  • @emersonsbr can explain this mistake better, so I can try to help you?

  • Shows the error message you are launching...

0

Solution assuming there is always caution, but using Uter Join

select m.cod_material,(m.qtd - c.qtd) qtd
from
(SELECT cod_material, (SUM(quantidade) qtd
 FROM lista_geral 
 WHERE local = 'xxx' 
 GROUP BY cod_material) m,
(SELECT cod_material,SUM(quantidade) qtd
 FROM cautela 
 WHERE local = 'xxx' 
 GROUP BY cod_material) c
where c.cod_material = m.cod_material

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.