mysql - Return a default value if single row is not found

Question

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?

Answer 1

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.