CODEWITHSUNDEEP
mysqlnullconditional-statementscoalesce
Anthony
Anthony

Reputation: 37065

MySQL coalesce for some null, not all null

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.

Upvotes: 1

Views: 2706

Answers (3)

jsimlo
jsimlo

Reputation: 36

Oh nooo, a join with two inner selects, one of them EXISTS conditional? Are you serious? How about using a simple UNION to fill in the blanks?? :) Something like

SELECT real_week_data UNION SELECT fake_empty_week;

This UNION would even unique the results for you, givin you exactly what you want..

Upvotes: 2

codemonkey
codemonkey

Reputation: 2665

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?

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425341

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

Upvotes: 0

Related Questions