Selection with LEFT JOIN

Question

I’m studying sql and I don’t know if this is possible. But I have the following tables:

Table contatos

______________________

id | nome | sobrenome |
______________________

That contains only one contact:

1 | andrei | coelho |

And I have another table:

Table contatos_telefone

____________________________

id | id_contato | telefone |
___________________________

In this table I have 2 records:

1 | 1 | 9999999 |
2 | 1 | 8888888 |

I used the LEFT JOIN as follows:

    SELECT contatos.id as id_contato, contatos.nome as nome, 
    contatos.sobrenome as sobrenome, contatos_telefone.telefone 
    FROM contatos LEFT JOIN contatos_telefone 
    ON contatos.id = contatos_telefone.id_contato
    ORDER BY contatos.nome

He returns it to me:

 [id_contato] => 1 [nome] => Andrei [sobrenome] => Coelho [telefone] => 9999999
 [id_contato] => 1 [nome] => Andrei [sobrenome] => Coelho [telefone] => 8888888

I was wondering if it’s possible to do something like this with the sql:

 [id_contato] => 1 [nome] => Andrei [sobrenome] => Coelho array(telefones 
 [0] => 8888888 [1] => 9999999)

Because I would like him to group by name. I tried to use the GROUP BY, but I couldn’t. He only brought me a phone record.

Thanks in advance!

Answer 1

You can use the GROUP_CONCAT

SELECT 
    contatos.id as id_contato, 
    contatos.nome as nome, 
    contatos.sobrenome as sobrenome, 
    GROUP_CONCAT(contatos_telefone.telefone SEPARATOR ',') AS telefones
FROM contatos 
LEFT JOIN contatos_telefone ON contatos.id = contatos_telefone.id_contato
GROUP BY contatos.id, contatos.nome, contatos.sobrenome
ORDER BY contatos.nome

And return all phones in one column.

Unless mistaken, if you do not inform SEPARATOR, it returns the array:

GROUP_CONCAT(contatos_telefone.telefone)