INSERT WITH JOIN DUPLICATING DATA

Question

INSERT WITH JOIN DUPLICATING DATA

Navigation

#1 by (2 votes)

0

Hello, I have these 3 tables in my database:

Table colaboradores
Table cursos
Table c_vendas_itens

I would like to do a join of the three tables and save to another one, I can do this through the query below, however every time I have a new data in the c_vendas_itens table I have to perform the join again and then the data gets duplicated, someone Do you know how I can solve this situation? I'm running this query on an insert trigger in the c_vendas_itens table.

    INSERT INTO c_tabela_auxiliar 
(
cod_venda_item,
status_venda,
cpf_colab,
cod_venda,
cod_curso,
nome_curso,
categoria_venda,
nome_colab,
banco,
dv,
agencia,
conta,
valor_venda_item,
valor_repasse_item
)
select 
ven.cod_venda_item,
ven.status_venda,
cur.cpf_colab,
ven.codVenda,
cur.codCurso,
cur.nome,
cur.categoria,
col.nome,
col.banco,
col.dv,
col.agencia,
col.conta,
ven.valorVendaItem,
ven.valorRepasse

from c_vendas_itens as ven 
inner join cursos as cur on (cur.codCurso = ven.cod_curso)               
inner join colaboradores as col on (col.cpf = cur.cpf_colab);

mysql join

asked by anonymous 02.01.2019 / 03:19

1 answer

Error in metadata type when I try to do POST Reading a file

score 2 · Accepted Answer

For the name that is in the cod_venda_item field, I imagine it to be a primary key with the identifier of each item for that particular sale.

Add in your query a LEFT JOIN with the same table being INSERT and see if the item does not exist in it to be added:

 INSERT INTO c_tabela_auxiliar 
 (
     cod_venda_item,
     status_venda,
     cpf_colab,
     cod_venda,
     cod_curso,
     nome_curso,
     categoria_venda,
     nome_colab,
     banco,
     dv,
     agencia,
     conta,
     valor_venda_item,
     valor_repasse_item
 )
 SELECT ven.cod_venda_item,
        ven.status_venda,
        cur.cpf_colab,
        ven.codVenda,
        cur.codCurso,
        cur.nome,
        cur.categoria,
        col.nome,
        col.banco,
        col.dv,
        col.agencia,
        col.conta,
        ven.valorVendaItem,
        ven.valorRepasse
 FROM c_vendas_itens as ven 
INNER JOIN cursos cur 
   ON (cur.codCurso = ven.cod_curso)               
INNER JOIN colaboradores col 
   ON (col.cpf = cur.cpf_colab)
 LEFT JOIN c_tabela_auxiliar aux
   ON aux.cod_curso = ven.cod_curso
  AND aux.cpf_colab = col.cpf
  AND aux.cod_venda_item = ven.cod_venda_item
WHERE aux.cod_venda_item IS NULL;