jojodog jojodog - 3 months ago 12
MySQL Question

How to SELECT with IF clause in mysql over query

I want to select columns

id_thread
,
no_tlp
, and
nama
(contact name)

But not all number are save on
tb_contact
(eg: sms addres from sms-gateway, they have auto name value)

So IF nama = NULL THEN SET the 'nama'='no_tlp'

SELECT
id_thread, no_tlp,
IF(tb_contact.`nama` = 'NULL' THEN SET nama = no_tlp) AS nama
FROM
tb_sms
LEFT JOIN
tb_contact ON tb_sms.`no_tlp` = tb_contact.`no_tlp`
GROUP BY
id_thread ASC


That's my query but it didn't work,

Somebody help me please

JPG JPG
Answer

Try this;)

SELECT id_thread, tb_contact.`no_tlp`, IF(tb_contact.`nama` IS NULL, tb_contact.`no_tlp`, tb_contact.`nama`) AS nama  
FROM tb_sms
LEFT JOIN tb_contact 
ON  tb_sms.`no_tlp` = tb_contact.`no_tlp` 
GROUP BY id_thread ASC

Or use COALESCE like:

SELECT id_thread, tb_contact.`no_tlp`, COALESCE(tb_contact.`nama`, tb_contact.`no_tlp`) AS nama  
FROM tb_sms
LEFT JOIN tb_contact 
ON  tb_sms.`no_tlp` = tb_contact.`no_tlp` 
GROUP BY id_thread ASC
Comments