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?
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
.