Select with fields without values

Question

I’m making a SELECT where I join two tables: a stock balance table and another order table.

I’m making a comparison between product balance in stock and balance on purchase orders, only the way I’m doing, the SELECT is only bringing products that have open purchase order balance in the system.

I need the SELECT bring not only products that have balance in order of purchases, but also all products of the independent products table whether have placed order of purchases or not. products which have not ordered purchases placed, and SELECT brings the value 0 in the column.

SELECT      DISTINCT SB1.B1_DESC            AS PRODUTO
        ,   SUM(SB8.B8_SALDO)               AS SALDO
        ,   (SC7.C7_QUANT - SC7.C7_QUJE)    AS [SALDO DE COMPRAS]
        ,   SC7.C7_NUM                      AS PEDIDO
        ,   SB1.B1_EMIN                     AS [SALDO MINIMO EM ESTOQUE]
FROM        SB1010 AS SB1
INNER JOIN  SB8010 AS SB8 WITH(NOLOCK) ON SB8.B8_PRODUTO = SB1.B1_COD
INNER JOIN  SC7010 AS SC7 WITH(NOLOCK) ON SC7.C7_PRODUTO = SB1.B1_COD
WHERE       ISB8.B8_LOCAL   =  '01'
        AND SC7.C7_ENCER    <> 'E'
        AND SB1.D_E_L_E_T_  =  ''
        AND SB8.D_E_L_E_T_  =  ''
        AND SC7.D_E_L_E_T_  =  ''
GROUP BY    SB1.B1_DESC
        ,   SB1.B1_EMIN
        ,   SC7.C7_QUANT
        ,   SC7.C7_QUJE
        ,   SC7.C7_NUM

OBS: In that SELECT have a filter SC7.C7_ENCER which means already completed purchase orders.

Answer 1

Must exchange the INNER JOIN for LEFT JOIN, thus preventing the table code SB1 must be included in the other two tables.

One aspect that may have an impact is that it does not validate NULL in the clause WHERE, which almost certainly prevents another result from being obtained.

I think that this should work:

SELECT      DISTINCT SB1.B1_DESC                    AS PRODUTO
        ,   SUM(ISNULL(SB8.B8_SALDO, 0))            AS SALDO
        ,   ISNULL((SC7.C7_QUANT - SC7.C7_QUJE, 0)) AS [SALDO DE COMPRAS]
            -- não sei o tipo da coluna, por isso coloquei "0"
            -- se for VARCHAR colocar '' ou outro valor por omissão em caso de NULL
        ,   ISNULL(SC7.C7_NUM, 0)                   AS PEDIDO
        ,   ISNULL(SB1.B1_EMIN, 0)                  AS [SALDO MINIMO EM ESTOQUE]
FROM        SB1010 AS SB1
LEFT JOIN   SB8010 AS SB8 (NOLOCK) ON SB8.B8_PRODUTO = SB1.B1_COD
LEFT JOIN   SC7010 AS SC7 (NOLOCK) ON SC7.C7_PRODUTO = SB1.B1_COD
WHERE       ISNULL(SB8.B8_LOCAL, '')    =  '01'
        AND ISNULL(SC7.C7_ENCER, '')    <> 'E'
        AND SB1.D_E_L_E_T_              =  ''
        AND ISNULL(SB8.D_E_L_E_T_, '')  =  ''
        AND ISNULL(SC7.D_E_L_E_T_, '')  =  ''
GROUP BY    SB1.B1_DESC
        ,   SB1.B1_EMIN
        ,   ISNULL(SC7.C7_QUANT, 0)
        ,   ISNULL(SC7.C7_QUJE, 0)
        ,   ISNULL(SC7.C7_NUM, 0)

Answer 2

Switch INNER JOIN to LEFT JOIN:

SELECT DISTINCT
SB1.B1_DESC       AS PRODUTO,
SUM(SB8.B8_SALDO) AS SALDO,
(SC7.C7_QUANT - SC7.C7_QUJE) AS [SALDO DE COMPRAS],
SC7.C7_NUM AS PEDIDO,
SB1.B1_EMIN AS [SALDO MINIMO EM ESTOQUE]
FROM SB1010 AS SB1
LEFT JOIN SB8010 AS SB8 WITH(NOLOCK) ON SB8.B8_PRODUTO = SB1.B1_COD
LEFT JOIN SC7010 AS SC7 WITH(NOLOCK) ON SC7.C7_PRODUTO = SB1.B1_COD
WHERE SB8.B8_LOCAL = '01'
AND SC7.C7_ENCER <> 'E'
AND SB1.D_E_L_E_T_ = ''
AND SB8.D_E_L_E_T_ = ''
AND SC7.D_E_L_E_T_ = ''
GROUP BY SB1.B1_DESC, SB1.B1_EMIN, SC7.C7_QUANT, SC7.C7_QUJE, SC7.C7_NUM