当前位置: 动力学知识库 > 问答 > 编程问答 >

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.

网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: