List phones on the same line

Asked

Viewed 42 times

0

i have a database that has the following tables: contact, phone and contact table_phone since the cardinality in my application has to be N:N. When I make a Join Inner, the return brings me, for example, 3 records if it is three phones, 4 records if there are 4 and so on. I need to group the phones in single record, since only this information changes, for example:

inserir a descrição da imagem aqui

CREATE DATABASE Agenda;
USE Agenda;

CREATE TABLE contato(

 id_contato INT NOT NULL AUTO_INCREMENT,
 tipo_contato INT NOT NULL,
 nome_contato VARCHAR(50),
 empresa_contato VARCHAR(50),
 cargo_funcao VARCHAR(25),
 departamento VARCHAR(20),
 CONSTRAINT pk_contato PRIMARY KEY(id_contato)
);

CREATE TABLE telefone(

 codigo_telefone INT NOT NULL AUTO_INCREMENT,
 tipo_telefone INT NOT NULL,
 operadora VARCHAR(10),
 numero_telefone VARCHAR(20),
 CONSTRAINT pk_telefone PRIMARY KEY(codigo_telefone)
);

CREATE TABLE contatoTelefone(

 contato_telefone INT NOT NULL AUTO_INCREMENT,
 contato INT NOT NULL,
 telefone INT NOT NULL,
 CONSTRAINT pk_contato_telefone PRIMARY KEY(contato_telefone),
 CONSTRAINT fk_contato_fone FOREIGN KEY(contato) REFERENCES 
 contato(id_contato),
 CONSTRAINT fk_fone_contato FOREIGN KEY(telefone) REFERENCES 
 telefone(codigo_telefone)
);

And the select:

SELECT contato.*, telefone.*, contatotelefone.* FROM contato
INNER JOIN contatotelefone ON contato.id_contato = 
contatotelefone.contato_telefone
INNER JOIN telefone ON contatotelefone.contato_telefone = 
telefone.codigo_telefone;

Para ver como está a relação contato/telefone, ou só dou um select na tabela 
contatotelefone:

SELECT * FROM contatotelefone;

How can I do this in mysql? It can be solved dynamically?

Note: each contact can have a maximum of 4 registered phone numbers.

Thanks in advance!

  • you have to do this in SQL, can’t be in programming lingugem?

  • Pass select or table structure.

  • group_concat https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-Concat concatenating only or https://stackoverflow.com/questions/20320631/how-to-pivot-a-table-in-mysql-using-casestatements com case (pivot)

  • The ideal would be in SQL Guilherme Costamilam, because I need to use to fill the fields when the user uses navigation buttons (first, previous, next and last) and I believe that s would solve everything in the programming, would be very loaded the code. Moreover, I believe that it is more correct to come from the bank already 'set up' to maintain a certain independence. Anyway, I’m open to suggestions.

  • So @Motta, but the Cat would return all phones in the same column, and it would be interesting if each number occupied its column. Also, if I have to set the values for each occurrence, it is not feasible.

  • If you have a "phone number" enter use CASE (https://forum.imasters.com.br/topic/564923-left-outer-join-duplicating/? do=findComment&comment=2250340)

  • Tried to get PIVOT ?

  • Yes, actually I searched a lot on the net before posting here. I saw some examples and even tried to adapt to my problem, but none solved, because most of them use an aggregation function over number, such as SUM() or MAX() and put the result under columns according to a cut number, which is not the case.

Show 3 more comments
No answers

Browser other questions tagged

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