INSERT WITH JOIN DUPLICATING DATA

Question

Hello, I have these 3 tables in my database:

Table colaboradores
Table cursos
Table c_vendas_itens

I would like to make a Join of the three tables and save in another one, I can do this through the query below, but each time I have a new data in the table c_vendas_items I have to perform the Join again and then the data is duplicated, Does anyone know how I can resolve this situation? I am running this query in an insert Trigger in the table c_vendas_items.

    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);

Answer 1

By the name on the field cod_venda_item, I’m guessing it’s a Primary key with the identifier of each item in that particular sale.

Add to your query a LEFT JOIN with the same table being made the 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;