Inner Join with Where and multiple tables

Asked

Viewed 90 times

1

I am mounting a query, where I need to Join between two tables (because the data will be in the main B but not in the complementary BC) and a third table that necessarily need data, which will always be in the main table B.

I tried as below, but returns syntax error. I searched here and did not find an example, any suggestion?

select DATE_FORMAT(b.dt_lancamento, '%d-%m-%Y') as dt_lancamento_F,
                      DATE_FORMAT(b.dt_abertura, '%d-%m-%Y') as dt_abertura_F,
                      DATE_FORMAT(b.dt_finalizacao, '%d-%m-%Y') as dt_finalizacao_F,
                      DATE_FORMAT(b.dt_prim_producao, '%d-%m-%Y') as dt_prim_producao_F,
                      DATE_FORMAT(bc.dt_solicitacao, '%d-%m-%Y') as dt_solicitacao_F,
                      DATE_FORMAT(bc.dt_alergenico_ti, '%d-%m-%Y') as dt_alergenico_ti_F,
                      DATE_FORMAT(bc.dt_auto_avaliacao_ti, '%d-%m-%Y') as dt_auto_avaliacao_ti_F,
                      DATE_FORMAT(bc.dt_cert_halal, '%d-%m-%Y') as dt_cert_halal_F,
                      DATE_FORMAT(bc.dt_cert_kosher, '%d-%m-%Y') as dt_cert_kosher_F,
                      DATE_FORMAT(bc.dt_cert_brc, '%d-%m-%Y') as dt_cert_brc_F,
                      DATE_FORMAT(bc.dt_cert_fssc, '%d-%m-%Y') as dt_cert_fssc_F,
                      DATE_FORMAT(bc.dt_cert_ifs, '%d-%m-%Y') as dt_cert_ifs_F,
                      DATE_FORMAT(bc.dt_controle_pragas, '%d-%m-%Y') as dt_controle_pragas_F,
                      DATE_FORMAT(bc.dt_apro_fornecedor, '%d-%m-%Y') as dt_apro_fornecedor_F
                      u.admin NIVEL, BC.*,B.*
                 FROM USUARIOS U,
                      BRIEFING B
                LEFT JOIN BRIEFING_COMPRAS BC ON bc.cod = b.cod
                WHERE B.responsavel = u.nome_completo
                  AND B.COD = 551
mysql sql database join inner-join
  • 1

    What is the syntax error? Indicate which error occurs.

1 answer

4


A comma was missing after the declaration of one of the fields:

SELECT DATE_FORMAT(b.dt_lancamento, '%d-%m-%Y') as dt_lancamento_F,
       DATE_FORMAT(b.dt_abertura, '%d-%m-%Y') as dt_abertura_F,
       DATE_FORMAT(b.dt_finalizacao, '%d-%m-%Y') as dt_finalizacao_F,
       DATE_FORMAT(b.dt_prim_producao, '%d-%m-%Y') as dt_prim_producao_F,
       DATE_FORMAT(bc.dt_solicitacao, '%d-%m-%Y') as dt_solicitacao_F,
       DATE_FORMAT(bc.dt_alergenico_ti, '%d-%m-%Y') as dt_alergenico_ti_F,
       DATE_FORMAT(bc.dt_auto_avaliacao_ti, '%d-%m-%Y') as dt_auto_avaliacao_ti_F,
       DATE_FORMAT(bc.dt_cert_halal, '%d-%m-%Y') as dt_cert_halal_F,
       DATE_FORMAT(bc.dt_cert_kosher, '%d-%m-%Y') as dt_cert_kosher_F,
       DATE_FORMAT(bc.dt_cert_brc, '%d-%m-%Y') as dt_cert_brc_F,
       DATE_FORMAT(bc.dt_cert_fssc, '%d-%m-%Y') as dt_cert_fssc_F,
       DATE_FORMAT(bc.dt_cert_ifs, '%d-%m-%Y') as dt_cert_ifs_F,
       DATE_FORMAT(bc.dt_controle_pragas, '%d-%m-%Y') as dt_controle_pragas_F,
       DATE_FORMAT(bc.dt_apro_fornecedor, '%d-%m-%Y') as dt_apro_fornecedor_F,
       u.admin NIVEL,
       BC.*,
       B.*
  FROM USUARIOS U
 INNER JOIN BRIEFING B ON B.responsavel = u.nome_completo
  LEFT JOIN BRIEFING_COMPRAS BC ON bc.cod = b.cod
 WHERE B.COD = 551

  • I had to take the comma after the table Usuarios, then it worked. Thank you very much!

Browser other questions tagged mysql sql database join inner-join

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