Sebastian <[EMAIL PROTECTED]> wrote on 06/05/2005 02:23:45 AM:
> I have two fields: topic | title
> topic does not always have data in it, so i want to select `title` when
> `topic` is null..
> i thought i could do this (does not work):
> IF(title IS NULL, topic, title) AS heading
> Thanks.
You can also use the COALESCE() function to get the _first_ non-null value
from a list of values. To use your example (and extend it by one
condition) assume for a moment that if title is also null you want the
value 'n/a' to appear. This is simple two-field failover (with a null
result if title is also null) exactly like the IF() and IFNULL() solutions
already posted:
SELECT COALESCE(topic, title) as heading from tablename;
This has the final "default value", avoiding a null result completely:
SELECT COALESCE(topic, title, 'n/a') as heading from tablename;
It works like this:
a) if topic is not null, return topic
b) if topic is null and title is not, return title
c) if both topic and title are null, return 'n/a'
IF there were 16 fields,formulas, or values in its list, COALESCE() would
have moved from term to term looking, checking all 16 items in turn, for
the first non-null. If every term is null, COALESCE() returns a NULL.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
