Replace column value based on subquery

Question

Basically, I have a table, call it regular, that gives me the service_id for a given day of the week, and then another table, call it exception, that specifies certain dates when the service_id is different from what it usually is. Treat the service_id as opaque strings.

regular’s structure:

+-------------+------------+
| day_of_week | service_id |
+-------------+------------+
| sunday      | some_serv  |
| monday      | another    |
| tuesday     | another    |

(It goes on for the rest of the week, days can often have the same service_id)

exception’s structure:

+-------------+------------+
| date        | service_id |
+-------------+------------+
| 2013-05-11  | different  |

(It has several dates like this)

Basically, what I want is a query that will return today’s service_id, which is the value for the day of the week unless there is an exception in the second table, in which case it should be that service_id.

I don’t see any good way to JOIN because the service_id columns are necessarily going to be different, and even with a JOIN I don’t know how to have the second table’s value replace the first table’s, which I need because I want this to be joined into a larger query so I can get just the results for today’s service_id (though I suppose I could do something like

WHERE (
    `exception`.`service_id`=Main.`service_id`
    OR
    (`exception`.`service_id` IS NULL AND `regular`.`service_id`=Main.`service_id`)
)

but that seems wasteful).

Changing the format of these tables is problematic for a variety of reasons.

Answer 1

Do the following

SELECT XXX,
       IFNULL(exception.service_id, weekdays.service_id)
FROM  maintable
 LEFT JOIN weekdays ON DAYOFWEEK(maintable.date) = weekdays.day_of_week 
 LEFT JOIN exceptions on maintable.date = exceptions.date

Of course, you would need to convert the integer return value of DAYOFWEEK to the string equivalent expected by the weekdays table.

You could do that by using a CASE statement in the JOIN condition:

SELECT XXX,
       IFNULL(exception.service_id, weekdays.service_id)
FROM  maintable
 LEFT JOIN weekdays ON weekdays.day_of_week 
                        = CASE WHEN DAYOFWEEK(maintable.date) = 1 THEN 'sunday'
                               WHEN DAYOFWEEK(maintable.date) = 2 THEN 'monday'
                               ..
                          END
 LEFT JOIN exceptions on maintable.date = exceptions.date

To only get the service_id, you could just fire two separate queries; if you absolutely want just one query, you can do the following:

SELECT IFNULL((SELECT service_id 
               FROM exceptions
               WHERE exceptions.date = $somevalue
              ),
              (SELECT service_id 
               FROM weekdays
               WHERE weekdays.day_of_week 
                        = CASE WHEN DAYOFWEEK($somevalue) = 1 THEN 'sunday'
                               WHEN DAYOFWEEK($somevalue) = 2 THEN 'monday'
                               ..
              )
             ) AS service_id