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:
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?
– Costamilam
Pass select or table structure.
– rbz
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)
– Motta
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.
– João F.S. Junior
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.
– João F.S. Junior
If you have a "phone number" enter use CASE (https://forum.imasters.com.br/topic/564923-left-outer-join-duplicating/? do=findComment&comment=2250340)
– Motta
Tried to get
PIVOT
?– rbz
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.
– João F.S. Junior