Problems with JOIN and CONDITIONS - sql

Question

Problems with JOIN and CONDITIONS - sql

Navigation

#1 by (0 votes)

0

I have to search 2% with some data with certain conditions, it follows the situation:

table : tbTransito = > table : Code - Qty - OrderID
Colunas : tbSet = > table : Code - Qtd - QtdLimite

My query should return the% Code , Qtd (tbEstock) + Qtd , (tbTransito) QtdLim
Qtd , Qtd Qtd , (tbTransito))

But every Colunas should add the Qtd s only if the s s are the same and if p>

- Code - Qty - Order No -
---- M01 ------ 5 ---------- 0 -----------
---- M02 ------ 2 ---------- 0 -----------
---- M03 ------ 4 -------- INV2 --------

- Code - Qtd - QtdLim -
---- M01 ------ 2 -------- 5 -------
---- M02 ------ 3 -------- 5 -------
---- M03 ------ 3 -------- 5 -------
---- M04 ------ 1 -------- 5 ------- ---- M02 ------ 1 -------- 5 ------

In a situation like this above the return would be just:

- Code - Qtd - QtdLim -
---- M03 ------ 3 -------- 5 -------
---- M04 ------ 1 -------- 5 -------

I'm trying the following code:

    select e.Codigo, 
           SUM(e.Qtd) + t.Qtd, 
           e.QtdLim 
    from tbEstoque e join tbTransito t on t.Codigo = e.Codigo 
    AND SUM(e.Qtd) + t.Qtd <= e.QtdLim 
    AND e.NumPedido = 0

But it is not returning right.

mysql sql

asked by anonymous 08.02.2018 / 20:01

1 answer

Changes to my site do not appear Error Unexpected token d Node

score 0 · Answer 1

When you use aggregation function ( sum, max, min, count ) in the query, you need a group by

select e.Codigo, 
           SUM(e.Qtd + t.Qtd), 
           e.QtdLim 
    from tbEstoque e join tbTransito t on t.Codigo = e.Codigo 
    where SUM(e.Qtd + t.Qtd) <= e.QtdLim 
    AND e.NumPedido = 0
    group by e.Codigo, e.QtdLim