List users' phones from a join without duplicating the rows

Navigation
2

Good evening,

Well, I have the following problem:

I need to join in two tables, USER and CONTACT. Each user has more than one contact (Phone), so when I 'm ready the data it returns me "duplicate" lines with different phones because, EX: 

ID  | Nome      | Telefone
1   | Felipe    | 968181444
1   | Felipe    | 37056781
2   | Claudio   | 37059999
2   | Claudio   | 968899999

Is it possible for me to create a phone column to list the data this way below? 

ID  | Nome      | Telefone 1    | Telefone 2
1   | Felipe    | 968181444     | 37056781
2   | Claudio   | 37059999      | 968899999

I researched a lot on google, I found something about PIVOT but I could not develop something similar to what I need.

Follow the query: 

SELECT u.ID, u.NOME, c.TELEFONE
FROM USUARIO u INNER JOIN CONTATO c
ON c.USUARIO_ID = u.ID
ORDER BY u.NOME

Any solution to this?

    
asked by anonymous 03.01.2019 / 03:25

1 answer

0

I got through the tip of graciomar. I put a subquery for phone 2 to create a column, with a change, I had to insert a column in the CONTACT table to know if the phone is 1st or 2nd. It's not the most elegant way but it does. 

SELECT DISTINCT u.ID as ID, u.NOME as NOME, (select 
t1.TELEFONE FROM CONTATO t1 WHERE t1.USUARIO_ID = c.ID AND t1.TELEFONE_POS = 
'1')  as TELEFONE, (select t2.TELEFONE FROM CONTATO t2 WHERE t2.USUARIO_ID = 
c.ID AND t2.TELEFONE_POS = '2') AS TELEFONE2
FROM USUARIO u INNER JOIN CONTATO c
ON c.USUARIO_ID = u.ID
ORDER BY u.NOME
    
03.01.2019 / 20:34
SheetJS, excel to mysql database Error Expected SSH_FXP_VERSION when connecting via php with phpseclib