I'm working with a little display complication here. I'm sure there's an IF/ELSE capability I'm just overlooking.

I have 2 tables I'm querying (customers, addresses). The first has the main record, but the second may or may not have a record to LEFT JOIN to.

I want to display a zero if there is no record in the addresses table. And I want to only display 1, if a record exists.

What I've attempted so far:

SELECT c.name, COALESCE(a.addressid,0) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123

This first example does not do it. But I may be utilizing COALESCE wrong.

How can I display a 0, if null, and a 1, if something exists?

0

7 Answers

Instead of COALESCE(a.addressid,0) AS addressexists, use CASE:

CASE WHEN a.addressid IS NOT NULL THEN 1 ELSE 0
END AS addressexists

or the simpler:

(a.addressid IS NOT NULL) AS addressexists

This works because TRUE is displayed as 1 in MySQL and FALSE as 0.

0

SELECT c.name, IF(a.addressid IS NULL,0,1) AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123

Careful if you're coming from C/C++ and expecting this to work:

select if(name, 1, 0) ..

Even if 'name' is not NULL, unlike in C, a false-condition still triggers and the above statement returns 0. Thus, you have to remember to explicitly check for NULL or empty string:

 select if(name is null or name = '', 0, 1)

PS Eugen's example up above is correct, but I wanted to clarify this nuance as it caught me by surprise.

SELECT c.name, CASE WHEN a.addressid IS NULL THEN 0 ELSE 1 END AS addressexists
FROM customers c
LEFT JOIN addresses a ON c.customerid = a.customerid
WHERE customerid = 123

Another method without WHERE, try this..

Will select both Empty and NULL values

SELECT ISNULL(NULLIF(fieldname,'')) FROM tablename

It will set null if it is an empty string, then be true on that also.

1

You can actually use an IF statement in the latest versions of MySQL.

IF(expr,if_true_expr,if_false_expr)

IE:

SELECT name, IF(ISNULL(name), 'robot', 'human') AS type
FROM visitors

If within TSQL, you can try :

SELECT IIF(a.addressid IS NULL, 0, 1) AS addressexists

SQL Server should work

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy