BUCKY or anyone please help me with this SQL query..please please please

+3 Ray Jung Bahadur · April 23, 2015
Ok Bucky, what do we do when , lets say the same boss wants to change the "NULL" to display as "N/A" ?


for this query 

SELECT customers.name, items.name FROM customers LEFT OUTER JOIN items ON customer.id=seller_id


the output is bunch of data with seller selling blah blah item. But what if it shows Bucky Roberts selling NULL ( my question is I want to replace displaying NULL as 'N/A') what do we do? 

Post a Reply

Replies

Oldest  Newest  Rating
0 Hidden Tesla · April 23, 2015
I went through this issue a day ago!!... You can us "IS" or "IS NOT" to check whether that field is null or not..

In my case i wanted to display the products which don't have NULL value so my query goes like this 
SELECT * FROM products,unit WHERE products.unit=unit.id AND quantity IS NOT NULL


so you can also go like this quantity IS NULL and than print N/A.
0 Dave . · April 23, 2015
You could use a case statement

You are going to have to test it I don't have time to setup to do it.

{your code}
SELECT
customers.name,
items.name
FROM customers
LEFT OUTER JOIN items ON customer.id=seller_id

If CASE does not work make sure the word NULL after WHEN does not need to be "NULL". "N/A" may not need the quotes. I placed them there because I have a feeling the / is not going to be liked in the code unless it is in quotes I could be wrong.

{modified to include case}
SELECT
customers.name,
items.name = CASE items.name
WHEN NULL THEN "N/A"
ELSE items.name
END
FROM customers
LEFT OUTER JOIN items ON customer.id=seller_id


This is a working case statement from code I wrote a few months ago. I used it as a reference.


StationNumber =  CASE PersonContactMethod.ContactMethodTypeId
WHEN 14 THEN Extension
ELSE NULL
END,
0 Dave . · April 23, 2015
For clarification

This is...


items.name = CASE items.name
WHEN NULL THEN "N/A"
ELSE items.name
END



The same as this...


anyvalue = CASE items.name
WHEN NULL THEN "N/A"
ELSE items.name
END



In a case statement the select value does not need to be an actual column name. The value you enter will become the column name in your report.
0 mike richardson · April 24, 2015
Why not use 

SELECT 
customers.name, 
COALESCE(items.name,'N/A') AS item
FROM customers 
LEFT OUTER JOIN items ON customer.id=seller_id
  • 1

SQL & Databases

106,995 followers
About

Everything SQL and Databases related in here!

Links
Moderators
Bucky Roberts Administrator