Select if null

[deleted]

In a select statement I have two fields. If one of the fields is NULL is want to select the other field. From what's I've read, the ISNULL function only returns a 1 or 0 but I'm looking for a way to return different fields depending on whether a field is null or not. Any ideas?

Roger_Ramjet

Well a Union query would do it. And probably joining the table to itself would work as well, but I'm not in the mood to work that one out.

$sql = "SELECT field1 as f FROM table WHERE f IS NOT NULL
UNION
SELECT field2 as f FROM table WHERE field1 IS NULL";

PS I'm not all that up on mysql syntax for the is not null, could be something like NOT IS NULL or NOT NULL.

mhalloran

Thanks for the post. I'll give that a try

tomhath

Or use COALESCE (can take as many fields as you want, the first non-null is returned):

select coalesce(field1, field2, 'default') as theField
from theTable

mhalloran

Thanks, that's exactly what I was looking for.