MySQL coalesce for some null, not all null

Question

I have a MySQL query that checks an employee schedule DB and (thanks to a previous stackoverflow question) returns any days that the user is not working as "No Work", so that if the user isn't working on Thursday, the query fills in that date even though the date does not appear in the original result set (and so I don't have to fill in those blanks via the PHP script).

Now the problem is that if the user is checking the schedule two weeks from now, it shows "No Work" for the entire week. If there are NO results, the script should output "Sorry, No Schedule posted for this week", so the user doesn't get mislead into thinking he has the week off and knows to check back later.

So the question is: What is the best query syntax for something like:

SELECT 
If Count(*) > 0 {
COALESCE(gooddata, nulldata) AS thedata
etc.
etc.
FROM sched_table
WHERE dates BETWEEN xxx AND yyy

So that it doesn't return any rows if all rows are null (or count is 0).

I tried adding a column for a count and having the script output "not posted" and quitting after one iteration, but then on good weeks it still quits after one iteration, so I'm getting frustrated.

I'm sure I can get the script to do what I want if I just take a step back, but I'm very curious if there is a way to do it in the query so that there has to be at least one row of non-Null values for it to coalesce the other null rows.

Answer 1

I'm actually of the opinion that you can go too far with using the database to do row-level formatting/conditional output operations. PHP is actually a lot faster at doing string comparisons and arithmetic than MySQL. The unwritten policy among developers I've worked with has always been that we don't use the database to format our output and I think you're getting into that territory with what you're doing here.

I'd be interested to know if others agree with this suggestion. I may actually post it as a new question... where does one draw the line in their use of the database to format output?

Answer 2

Most probably your query joins with a some kind of rowset to generate all days of week.

Add a condition into that rowset:

SELECT  COALESCE(gooddata, nulldata) AS thedata
FROM    (
        SELECT  *
        FROM    days_of_week
        WHERE   EXISTS
                (
                SELECT  NULL
                FROM    sched_data
                WHERE   dates BETWEEN xxx AND yyy
                )
        )
LEFT OUTER JOIN
        sched_data
WHERE   dates BETWEEN xxx AND yyy