Problems with JOIN and CONDITIONS - sql

Asked

Viewed 48 times

0

I’m having to fetch in 2 table some data with certain conditions, follows the situation:

table: tbTransmitted => Colunas: Code - Qty - On request
table: tbEstoque => Colunas: Code - Qty - Qtdlimite

My query should return the colunas Code, (Qtd(tbEstoque) + Qtd,(tbTransmitted)), Qty
whenever (Qtd(tbEstoque) + Qtd,(tbTransmitted)) <= Qty

But each rowshould add the Qtds only if the Codeare the same and if in At a request = 0

-- Code -- Qty -- On request --
---- M01 -------- 5 ---------- 0 -----------
---- M02 ------- 2 ----------- 0 -----------
---- M03 -------- 4 -------- INV2 --------

-- Code -- Qty -- Qty --
---- 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 -- Qty -- Qty --
---- 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’s not coming back right.

mysql sql

  • try using a Where after the column Join instead of directly ...from tbEstoque e join tbTransito t on t.Codigo = e.Codigo where SUM(e.Qtd) + t.Qtd <= e.QtdLim ...

  • also show how the return is coming, if you can

  • @Jetersonmirandagomes the return is still the same mistake. - error#1111 - Invalid use of group Function, which leads me to believe that it is the fault of SUM(), because by taking it out the query returns results: 2 Rows => ** M02 / 5 / 5** and another M02 / 3 / 5

  • group by e.codigo, e.qtdeLim puts below the sql code

1 answer

0

When using function aggregation(sum, max, min, count) in consultation, 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

Browser other questions tagged mysql sql

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