CODEWITHSUNDEEP
mysql
Anthony
Anthony

Reputation: 37065

combining "no results" second query with first query in MySQL

This is probably very easy, but I'm wanting to know the "best" or "standard" way of writing this query.

I have two tables. One is a work schedule with a task name and start timestamp and end timestamp. This table is populated by a schedule manager ahead of time.

UserId |   task |        startTime |          endTime
   joe | dishes | 2010-05-15 10:00 | 2010-05-15 14:00
   joe | papers | 2010-05-15 14:00 | 2010-05-15 15:00
   joe |  trash | 2010-05-16 09:00 | 2010-05-16 10:00
   joe |cooking | 2010-05-16 11:30 | 2010-05-16 13:30

The second table is a work log with the number of minutes worked per day and a datestamp. This table is populated by the employee.

UserId |       date | timeLogged
   joe | 2010-05-15 |        300
   joe | 2010-05-16 |        180

The way my app works currently is it queries the workLog table first to see if the user has already logged hours for a given date range. If the query returns 0 rows, a second courtesy query is done against the schedule table, so that the form is pre-filled, making it easier for the user to make adjustments, etc.

What I would like is a query that does both in one go. The problem is that it should only do the "second" query if the "first" is null, so doing any joins or if nulls seems incorrect to me, since it would combine results of both queries when the workLog table needs to prevail. For instance, if the user was sick on Tuesday and Wednesday, but logged hours for Monday and Thursday, I don't want results that use the logged hours from Monday and Thursday and the scheduled hours from Tuesday and Wednesday. But if he was sick every day that week, it's okay to populate the form with the schedule table data, as the user would not (or at least should not) be submitting a form.

I can think of one or two approaches, mainly using a third aggregate query and a UNION statement, but that seems sloppy.

Update

Here are the queries I'm wanting to combine:

SELECT date, timeLogged
FROM workLog
WHERE userID = '$userid'
AND date BETWEEN $startDate AND $endDate

If that returns no results:

SELECT DATE(startTime) AS date, 
SUM(TIME_TO_SEC(TIMEDIFF(startTime,endTime)))/60 AS timeLogged
FROM schedule
WHERE userID = '$userid'
AND date BETWEEN $startDate AND $endDate
GROUP BY DATE(startTime)

So any functions that follow after both queries don't need to know which table the results came from.

Upvotes: 2

Views: 125

Answers (1)

Álvaro González
Álvaro González

Reputation: 146460

Those queries return different stuff. You can only combine them into one sentence if you join them by user and date and then getting duplicate rows for worklog:

SELECT ws.UserId, ws.task, ws.startTime, ws.endTime, wl.timeLogged
FROM workSchedule ws
LEFT JOIN workLog wl ON ws.UserId=wl.UserId
    AND DATE(ws.startTime)=wl.date
WHERE UserId='joe'

would return:

UserId |   task |        startTime |          endTime | timeLogged
   joe | dishes | 2010-05-15 10:00 | 2010-05-15 14:00 |        300
   joe | papers | 2010-05-15 14:00 | 2010-05-15 15:00 |        300
   joe |  trash | 2010-05-16 09:00 | 2010-05-16 10:00 |        180
   joe |cooking | 2010-05-16 11:30 | 2010-05-16 13:30 |        180
   joe | dishes | 2010-05-17 09:00 | 2010-05-17 13:30 |       NULL
   joe | papers | 2010-05-17 13:30 | 2010-05-17 15:00 |       NULL

... and only considering the startTime. (Can you ahve a range like 2010-05-17 23:30 — 2010-05-18 00:30?)

Honestly, I don't think it makes sense. I'd keep them separate.

Upvotes: 2

Related Questions