How can I select same column value as different column from mysql table with join?

Question

I have a table in mysql datbase name as communication and its columns and values are:

and contact table and its columns and values are:

From above tables I have a requirement to select values like following:

contact_id f_name l_name phone_number email_address creation_date

Actually I am not a database programmer. And you may ask what have you tried so far?. I do not have any idea to try this. Every problem has solution so I asked here instead to change database design and also do not know is this stupid idea or not. If anyone have solution it would be great help for me.

Note: email_address and phone_number comes from communication table with the help of medium field. And communication table can have only two medium i.e. MAIL and PHONE. EMAIL was wrong record so EMAIL is also MAIL.

Thanks

Answer 1

You have to join communication twice, depending on medium. This should do the job (untested):

SELECT con.contact_id, con.f_name, con.l_name, com1.value AS phone_number, com2.value AS email_address, con.creation_date
FROM contact con 
LEFT JOIN communication com1 ON com1.contact_id = con.id AND com1.medium = 'PHONE'
LEFT JOIN communication com2 ON com2.contact_id = con.id AND com2.medium LIKE '%MAIL%'

Answer 2

SELECT CC.contact_id, f_name, l_name, CASE WHEN medium='PHONE' THEN  value ELSE value END 'communication', creation_date
FROM COntact C
JOIN Communication CC on CC.contact_id = C.id
GROUP BY CC.contact_id

... maybe, maybe not?

OR You could join Communication twice with medium as the flag:

SELECT id, f_name, l_name, M.value 'email_address', P.value 'phone_number' ,creation_date
FROM Contact C
JOIN Communication P on P.contact_id = C.id AND medium = 'PHONE'
JOIN Communication M on M.contact_id = C.id AND medium = 'MAIL'
GROUP BY id

?

Answer 3

You can pivot the table communication and join it with contact table

select contact_id,  f_name,  l_name,  phone_number, email_address, creation_date
from contact c 
JOIN (SELECT  contact_id,  
 GROUP_CONCAT(if(Medium= 'MAIL' or Medium= 'EMAIL', value, NULL)) AS 'Email_address', 
 GROUP_CONCAT(if(colID = 'PHONE', value, NULL)) AS 'phone_number'
FROM communication 
GROUP BY contact_id) med
On med.contact_id=c.id;

Please note Medium= 'MAIL' or Medium= 'EMAIL' is pivot as email_address.