Return a default value if single row is not found

default, fallback, mysql, null-coalescing, select

Solution

One way to do it

SELECT IFNULL(MIN(`file`), 'default.webm') `file` 
  FROM `show`, `schedule` 
 WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
   AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
 ORDER BY `start_time` DESC LIMIT 1

Since you return only one row, you can use an aggregate function, in that case `MIN()`, that ensures that you'll get `NULL` if no records selected. Then `IFNULL()` or `COALESCE()` will do its job.

Problem

I have the following select statement to grab the next scheduled item for a stream. If there is no matching row, I want it to return a default value. Here's the SQL that I'm using: ``` SELECT `file` FROM `show`, `schedule` WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` ORDER BY `start_time` DESC LIMIT 1 ``` That should grab the most recently scheduled item, but not if it's older than 30 minutes before the query. However, if the user doesn't schedule anything, I want a default value, so that something actually plays on the stream. I've tried the following: ``` SELECT COALESCE(`file`, 'default.webm') FROM `show`, `schedule`... ``` And ``` SELECT IFNULL(`file`, 'default.webm') FROM `show`, `schedule` ``` However, it always returns an empty result if no rows are found. How can I return a default value instead?

Original source