Bring only one record in a left Join even if there are more records in the relationship

Asked

Viewed 23 times

0

Hello, how are you?

This is my query:

SELECT cf.id_clifor, 
       cf.nome, 
       lv.localvenda, 
       ed.cidade, 
       ctt.telefone, 
       cf.descontoglobal, 
       lv.id_localvenda 
FROM   ((tbl_clientes_fornecedores AS cf 
         INNER JOIN tbl_local_de_venda AS lv 
                 ON cf.id_localvenda = lv.id_localvenda) 
        LEFT JOIN tbl_enderecos AS ed 
               ON cf.id_clifor = ed.id_clifor) 
       LEFT JOIN tbl_contatos_clientes AS ctt 
              ON cf.id_clifor = ctt.id_clifor 
WHERE  (( ( cf.status ) = 'ATIVO' )) 
ORDER  BY cf.nome; 

The results are like this:

Assim são os resultados

The point is that in tbl_contacts_clients and tbl_addresses, you may have more than one phone/address, but I would like the results to return me only 1 single record. I tried DISTINCT and a few more things without success. I use ACCESS.

Thanks for the help.

  • then you need to group, the group by will solve this, see this, can help: https://answall.com/a/228296/57220

  • good, it worked, thank you

No answers

Browser other questions tagged

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