Doubt with SQL Query

Question

Doubt with SQL Query

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have a stock movement table with the following columns and data:

table moves

id | operacao | prodinsumo | qtde
----------------------------------
1  | 1        | 25         | 6
2  | 2        | 10         | 3
3  | 10       | 17         | 1
4  | 11       | 10         | 2

table supplies :

codinsumo | nomeinsumo
------------------------
10        | água mineral

table products :

codproduto | nomeproduto
---------------------------    
25         | refrigerante
17         | espetinho

In the column operacao are stored the codes of operations that are:

1 - Sell Product

2 - Sale Input

10 - Product Reversal

11 - Reversal Input

The column prodinsumo stores the input or product code according to the operation.

I would like to be able to do a query that puts the name of the input or the product according to the operation, but I do not know how to do it.

Here is a query that I used to select the columns, but it does not decide which table will get the name, if it is that of the inputs or products.

select
case m.operacao
  when 1 then "Venda produto"
  when 2 then "Venda insumo"
  when 10 then "Estorno produto"
  when 11 then "Estorno insumo"
end as operacao,
i.nomeinsumo as nome, -- ou p.nomeproduto as nome
m.qtde
from movimentacoes m
left join produtos p on m.prodinsumo = p.codproduto
left join insumos i on m.prodinsumo = i.codinsumo

I need that when the column operacao is with the values 1 or 10 the column nome is with the value of column nomeproduto of table produtos and when it has the values 2 % or 11 is with the value of column nomeinsumo of table insumos .

sql

asked by anonymous 03.04.2017 / 21:00

2 answers

1

select
case m.operacao
  when 1 then "Venda produto"
  when 2 then "Venda insumo"
  when 10 then "Estorno produto"
  when 11 then "Estorno insumo"
end as operacao,
case m.operacao
  when 1 then (select nomeproduto from produtos where codproduto = 1  LIMIT 1)
  when 2 then (select nomeinsumo from insumos where codinsumo = 2  LIMIT 1)
  when 10 then (select nomeproduto from produtos where codproduto = 10  LIMIT 1)
  when 11 then (select nomeinsumo from insumos where codinsumo = 11  LIMIT 1)
end as nome,
m.qtde
from movimentacoes m
left join produtos p on m.prodinsumo = p.codproduto
left join insumos i on m.prodinsumo = i.codinsumo

03.04.2017 / 22:29

Leave a fixed option in a PivotTable - JS PHP Field Comparison

score 0 · Accepted Answer

After the response from @PedroE. I was able to do the following query that solved the problem in a better way.

This query is taking into account that, when the operations of the products are not found, the operations will be inputs, since there are no operations other than those that are in the question.

select
case m.operacao
  when 1 then "Venda produto"
  when 2 then "Venda insumo"
  when 10 then "Estorno produto"
  when 11 then "Estorno insumo"
end as operacao,
case m.operacao
  when 1 then p.nomeproduto
  when 10 then p.nomeproduto
  else i.nomeinsumo
end as nome, 
m.qtde
from movimentacoes m
left join produtos p on m.prodinsumo = p.codproduto
left join insumos i on m.prodinsumo = i.codinsumo