CODEWITHSUNDEEP
phpmysql
Shivam Pandya
Shivam Pandya

Reputation: 1061

Mysql Join 2 Table and merge 2 Column, But remove Duplicate

I have 2 Table from where I want customerid, customername, comment and customercontactno.

I use Following query For Join 2 Table.

SELECT comment.id, comment.Kommentar, comment.Kunde,
CONCAT_WS('', customer.telefonPrivat, customer.TelefonMobil) AS Contact_Phone 
FROM tbl_test_comment comment 
LEFT JOIN tbl_test_customer customer 
ON customer.id = comment.Kunde;

My First table is tbl_test_comment With following data

enter image description here

And tbl_test_customer

enter image description here

Result Of Above Query

enter image description here

ISSUE

When I run above query, Its working fine if one of two merged column is empty. But it merge data if data are in both row. I want to avoid one if both row have value.

Expected Output

enter image description here

Upvotes: 0

Views: 363

Answers (1)

Andomar
Andomar

Reputation: 238078

concat_ws stands for "concatenate with separator", that is, add the strings together with the separator in between.

Instead, use the coalesce function, which returns the first non-null argument:

coalesce(customer.telefonPrivat, customer.TelefonMobil)

If an empty telephone number can be an empty string '' as well as null, you can use the more powerful case statement:

case 
when length(customer.telefonPrivat) > 0 then customer.telefonPrivat
else customer.TelefonMobil
end

Upvotes: 1

Related Questions