Repeated data with GROUP_CONCAT

Question

Repeated data with GROUP_CONCAT

Navigation

#1 by (1 votes)

0

I'm having a problem when I make a SELECT in my DB, I'm using GROUP_CONCAT to concatenate the phone and email column but I'm getting repeated data.

Here is SELECT :

SELECT contatos.cont_id
     , contatos.cont_firstname
     , contatos.cont_lastname
     , contatos.data_nasc
     , contatos.anotacoes
     , GROUP_CONCAT(contatos_email.email)
     , contatos_telefone.telefone
     , contato_endereco.rua
     , contato_endereco.numero
     , contato_endereco.bairro
     , contato_endereco.cidade
     , contato_endereco.estado
     , contato_endereco.pais 
FROM contatos 
JOIN contatos_email ON contatos.cont_id = contatos_email.contato_id 
JOIN contatos_telefone ON contatos.cont_id = contatos_email.contato_id
JOIN contato_endereco ON contatos.cont_id = contato_endereco.contato_id

In the column in which the emails are being displayed, it was only for 3 emails to appear and not repeat as it is now ...

sql group-concat

asked by anonymous 10.06.2016 / 06:23

1 answer

Problem with scanf on% d receiving a keyboard letter Is there any risk in using the Django Token Generator in different services?

score 1 · Accepted Answer

The solution was this

SELECT contatos.cont_id, 
contatos.cont_firstname,
contatos.cont_lastname, 
contatos.data_nasc, 
contatos.anotacoes,
GROUP_CONCAT(distinct contatos_email.email),
GROUP_CONCAT(DISTINCT contatos_telefone.telefone), 
contato_endereco.rua, contato_endereco.numero, 
contato_endereco.bairro, contato_endereco.cidade, 
contato_endereco.estado, contato_endereco.pais 
FROM contatos 
LEFT JOIN contatos_email ON contatos.cont_id = contatos_email.contato_id 
LEFT JOIN contatos_telefone ON contatos.cont_id =  contatos_email.contato_id
LEFT JOIN contato_endereco ON contatos.cont_id = contato_endereco.contato_id 
group by contatos.cont_id