Selection with LEFT JOIN

Question

Selection with LEFT JOIN

Navigation

#1 by (3 votes)

2

I'm studying sql and I do not know if this is possible. But I have the following tables:

Table contatos

______________________

id | nome | sobrenome |
______________________

That only contains 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 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

It returns me this:

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

I wanted to know if it's possible to do something like this with sql :

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

Well, I'd like him to group by name. I tried to use GROUP BY , but I could not. He brought me just one phone record.

Thank you in advance!

mysql sql procedure

asked by anonymous 24.03.2018 / 22:44

1 answer

Error: "The BookmarkColumns symbol could not be found" How to "copy" text from one div to another with JQuery?

score 3 · Accepted Answer

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 the phones in a single column.

Except for a mistake, if you do not enter SEPARATOR , it returns the array:

GROUP_CONCAT(contatos_telefone.telefone)