Axeem Axeem - 2 months ago 18
MySQL Question

Changing phone number with contact name

I have a table for contacts like

Contacts
number (Primary key)
name (name of the contact)

Messages
id (primary key)
number (reciever or sender's phone number)
type (sent or recieved)
text (message body)
date (timestamp)


What I want is, when I query the messages, and group them by phone number to make a conversation, i would like to change the phone number in the Messages table to change with the name of the contact in contacts table if present, if not present leave the phone number as a phone number as there are unknown numbers who can text too. PS I have googled a lot to find a solution but didn't find one.

For example,

Contacts table contain
id name
1 a
2 b

Messages Contain
id number type text date
1 1 sent haha 123456
2 3 recieved hi 123459


Now result should be

number type text date
a sent haha 123456
3 received hi 123459

Answer

You need to use LEFT JOIN between Messages table and Contacts table.

SELECT 
 COALESCE(c.name,m.id) AS number,
 m.type,
 m.text,
 m.date
FROM messages m 
LEFT JOIN contacts C  ON m.number = C.id;

Note:

MySQL COALESCE() function returns the first non-NULL value of a list, or NULL if there are no non-NULL values.

mysql> SELECT COALESCE(NULL, 2, 3);
+----------------------+
| COALESCE(NULL, 2, 3) |
+----------------------+
|                    2 |
+----------------------+
1 row in set (0.02 sec)
mysql> SELECT COALESCE(NULL, NULL, NULL);
+----------------------------+
| COALESCE(NULL, NULL, NULL) |
+----------------------------+
|                       NULL |
+----------------------------+
1 row in set (0.00 sec)
Comments