INNER JOIN + LEFT JOIN

Question

INNER JOIN + LEFT JOIN

Asked 7 years ago

Viewed 711 times

2

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.

I haven’t seen how I could do it yet. Could you show me how?

– Bruno Queiroz

2017/10/25 at 17:17
Basicamente TAFGRPROD.CDGRPR é chave estrangeira da tabela TAFPROD, TAFCBHISTPED.NUPED e TAFPROD.CDPROD são chaves estrangeiras da tabela TAFITHISTPED.

– Bruno Queiroz

2017/10/25 at 17:33
See if my answer helps you.

– Caique Romero

2017/10/25 at 17:40

2 answers

2

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

1

It worked very well! Thank you very much!!!!!

– Bruno Queiroz

2017/10/25 at 17:46

Browser other questions tagged sql sql-server sql-server-2014

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

by Marco Souza • **12,304** points · Answer 1 · 2017-10-25T17:23:01+00:00

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