Repeated data with GROUP_CONCAT

Asked

Viewed 422 times

0

I have a problem when I make one SELECT in my comic book, I’m using GROUP_CONCAT to concatenate the column of phones and email but I’m picking up repeated data.

Here is the 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 where the emails are being displayed was to appear only 3 emails and not keep repeating like this now... inserir a descrição da imagem aqui

inserir a descrição da imagem aqui

sql group-concat
  • 1

    Friend, add an example of how the data is currently being returned and how you want it to be the final result. It will facilitate the understanding of your question.

  • 1

    You are using GROUP_CONCAT without doing GROUP or any aggregation function. What you want to happen?

  • ready I edited the question... Now I think you can understand better :)

  • Tried to GROUP_CONCAT( DISTINCT contatos_email.email )? What is the DB server you are using?

  • I’m using maria db in phpmyadmin

  • 1

    @Bacco worked I did as you said put the GROUP_CONCAT( DISTINCT contacts_email.email) and worked Vlw :)

  • Good that solved. As you have posted the solution, mark as solved in the green V side of your reply, for the post to be complete.

Show 2 more comments

1 answer

1


That was the solution 

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

inserir a descrição da imagem aqui

Browser other questions tagged sql group-concat

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