INNER JOIN + LEFT JOIN

Question

INNER JOIN + LEFT JOIN

Navigation

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

2

I'm trying to query the data in 4 tables :

TAFCBHISTPED = NUPED, DTEMIS

TAFGRPROD = CDGRPR, DSGRPR

TAFPROD = CDPROD, CDGRPR

TAFITHISTPED = NUPED, CDPROD, QTPED

My query looked like this:

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 multipart identifier " TAFGRPROD.CDGRPR " can not be associated.

sql sql-server sql-server-2014

asked by anonymous 25.10.2017 / 19:05

2 answers

1

I believe this is the failure to create the alias for your TAFGRPROD table, or rather to 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

25.10.2017 / 19:23

Text break by delimiter SQL Procedure (executed every second)

score 2 · Accepted Answer

All tables have been listed, try select 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