CODEWITHSUNDEEP
mysql
Kushal
Kushal

Reputation: 47

Mysql customer_details display other column value if address is missing

I wrote a query to display the customer id, customer name and contact details of customers and if address is missing then display the email id and if both address and email is missing then display ‘NA’ and i am sorting the results based on customer id but i am not getting any output.

My table structure

customer_id VARCHAR(10)
customer_name VARCHAR(20)
address VARCHAR(20)
phoneno BIGINT
email_id VARCHAR(20)

select customer_id,customer_name,phone_no,email_id from customers ,
where
if(address isnull) and if(email_id isnull) then 
"NA"
if(address isnull) then 
email_id
order by customer_id;

Firing the Query

Program Outpput
customer_id customer_name   phone_no    email_id    address
C001    Amith   9843123467  [email protected] Bangalore
C002    Anand   9487256231  NULL    NA
C003    Bala    9442478962  [email protected]  Chennai
C004    Gowri   9487267845  [email protected] Coimbatore
C005    Priya   9843134523  [email protected] Cochin
C006    Prem    9528431343  [email protected]  Coimbatore
C007    Rahul   9513428433  [email protected] [email protected]
C008    John    9982843342  [email protected]  Chennai
C009    Abdul   9843065418  [email protected] [email protected]

Expected Output
customer_id customer_name   contact_details
C001    Amith   Bangalore
C002    Anand   NA
C003    Bala    Chennai
C004    Gowri   Coimbatore
C005    Priya   Cochin
C006    Prem    Coimbatore
C007    Rahul   [email protected]
C008    John    Chennai
C009    Abdul   [email protected]

Upvotes: 1

Views: 21980

Answers (4)

Aishwarya Nadimuthu
Aishwarya Nadimuthu

Reputation: 1

select customer_id,customer_name,
case
when(phone_no is null and address is null) then
email_id 
when(phone_no is null) then 
address
else    
phone_no    
end contact_details from customer order by customer_id asc;

Upvotes: 0

josh
josh

Reputation: 31

select customer_id,customer_name,
case
  when email_id is null and address is null then 'NA'
  when address is null then email_id
  else address
end as 'CONTACT_DETAILS'
FROM customers
order by customer_id;

Upvotes: 3

Rohith Devisetty
Rohith Devisetty

Reputation: 1

SELECT customer_id, customer_name, coalesce(address, email_id, 'NA') AS contact_details FROM customers ORDER BY customer_id ASC;

Upvotes: 0

veena
veena

Reputation: 31

SELECT customer_id, customer_name, COALESCE(address, email_id, 'NA')
AS contact_details
FROM customers;

Upvotes: 3

Related Questions