INNER JOIN + LEFT JOIN

Question

I’m trying to make a query where the data lies in 4 tables:

TAFCBHISTPED = NUPED, DTEMIS

TAFGRPROD = CDGRPR, DSGRPR

TAFPROD = CDPROD, CDGRPR

TAFITHISTPED = NUPED, CDPROD, QTPED

My query stayed that way:

SELECT
    TAFCBHISTPED.NUPED,
    TAFCBHISTPED.DTEMIS,
    TAFGRPROD.CDGRPR,
    TAFGRPROD.DSGRPR,
    TAFPROD.CDPROD,
    TAFPROD.CDGRPR,
    TAFITHISTPED.NUPED,
    TAFITHISTPED.CDPROD,
    TAFITHISTPED.QTPED
FROM dbo.TAFCBHISTPED
INNER JOIN dbo.TAFITHISTPED ON TAFCBHISTPED.NUPED = TAFITHISTPED.NUPED
LEFT JOIN dbo.TAFPROD ON TAFGRPROD.CDGRPR = TAFPROD.CDGRPR

But I get the following error:

Sqlstate: 42000 Error Code: 4104 Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The multi-part identifier "TAFGRPROD.CDGRPR" cannot be associated.

Answer 1

Failed to list all tables, try the select down below.

SELECT
    TAFCBHISTPED.NUPED,
    TAFCBHISTPED.DTEMIS,
    TAFGRPROD.CDGRPR,
    TAFGRPROD.DSGRPR,
    TAFPROD.CDPROD,
    TAFPROD.CDGRPR,
    TAFITHISTPED.NUPED,
    TAFITHISTPED.CDPROD,
    TAFITHISTPED.QTPED
FROM dbo.TAFCBHISTPED
INNER JOIN dbo.TAFITHISTPED 
   ON TAFCBHISTPED.NUPED = TAFITHISTPED.NUPED
LEFT JOIN dbo.TAFPROD -- Adiciono a relação de TAFITHISTPED com TAFPROD
   ON TAFITHISTPED.CDPROD = TAFPROD.CDPROD
LEFT JOIN dbo.TAFGRPROD -- Aqui incluo a tabela que ficou faltando
  ON TAFGRPROD.CDGRPR = TAFPROD.CDGRPR

Answer 2

I believe this is the lack of creating the alias for your TAFGRPROD table, or rather specify it next to the from.

    SELECT
        TAFCBHISTPED.NUPED,
        TAFCBHISTPED.DTEMIS,
        TAFGRPROD.CDGRPR as 'TAFGRPROD.CDGRPR',
        TAFGRPROD.DSGRPR,
        TAFPROD.CDPROD,
        TAFPROD.CDGRPR as 'TAFPROD.CDGRPR',
        TAFITHISTPED.NUPED,
        TAFITHISTPED.CDPROD,
        TAFITHISTPED.QTPED
    FROM dbo.TAFCBHISTPED
    INNER JOIN dbo.TAFITHISTPED ON TAFCBHISTPED.NUPED = TAFITHISTPED.NUPED

-- veja aqui a inclusão da sua tabela.
INNER JOIN dbo.TAFGRPROD ON TAFGRPROD.CDGRPR = TAFPROD.CDGRPR

    LEFT JOIN dbo.TAFPROD ON TAFGRPROD.CDGRPR = TAFPROD.CDGRPR