MYSQL查询不返回所有结果

I have a view table set up with the following fields:

我有一个视图表设置与以下字段:

paymentID, jobID, jobNumber, jobType, countType, countID, salesRep, dealershipName, checkTS, paymentAmount, estimatedMailArrival

paymentID,jobID,jobNumber,jobType,countType,countID,salesRep,dealershipName,checkTS,paymentAmount,estimatedMailArrival

I need to select these fields when checkTS falls between a certain date range. (checkTS is a datetime field). On top of these fields, I need to select two timestamps from another table named jobtasks (they may or may not exist, so I'll be using left joins). To do this, I have set up the following query:

当checkTS介于某个日期范围之间时,我需要选择这些字段。 (checkTS是日期时间字段)。在这些字段之上,我需要从另一个名为jobtasks的表中选择两个时间戳(它们可能存在也可能不存在,因此我将使用左连接)。为此,我设置了以下查询:

SELECT 
    s.jobID, s.jobNumber, s.jobType, s.countType, s.countID, s.salesRep,           s.dealershipName, s.checkTS, s.paymentID, s.paymentAmount, s.estimatedMailArrival, 
    jt1.completedTimestamp as Art, 
    jt2.completedTimestamp as List
FROM salesboard s
LEFT JOIN jobtasks jt1 ON s.jobID = jt1.jobID
LEFT JOIN jobtasks jt2 ON s.jobID = jt2.jobID
WHERE
    s.checkTS BETWEEN '2013-03-01 00:00:00' AND '2013-03-31 23:59:59' AND
    jt1.taskID = 22 AND
    jt2.taskID = 23
ORDER BY s.checkTS DESC;

The query doesn't return any errors, but I've found that it's not pulling all the records (it's pulling 182 of the 242 that should be pulled). I can manually go into the database (mysql) and view records that should be pulled, but aren't. One example - a timestamp of "2013-03-04 10:11:00" is not being pulled.

查询不会返回任何错误,但我发现它并没有提取所有记录(它正在拉动应该被拉出的242个中的182个)。我可以手动进入数据库(mysql)并查看应该提取的记录,但不是。一个例子 - “2013-03-04 10:11:00”的时间戳没有被提取。

If i remove all the Art and List stuff, i get the correct number of results... 242.

如果我删除所有艺术和列表的东西,我得到正确数量的结果... 242。

SELECT 
    s.jobID, s.jobNumber, s.jobType, s.countType, s.countID, s.salesRep, s.dealershipName, s.checkTS, s.paymentID, s.paymentAmount, s.estimatedMailArrival
FROM salesboard s
WHERE
    s.checkTS BETWEEN '2013-03-01 00:00:00' AND '2013-03-31 23:59:59'
ORDER BY s.checkTS DESC;

Why would some records not be returned if i'm using a left join?

如果我使用左连接,为什么不会返回某些记录?

2 个解决方案

#1

When using a LEFT JOIN, it doesn't do you any good to add fields from those tables to your WHERE criteria -- same as an INNER JOIN at that point.

当使用LEFT JOIN时,将这些表中的字段添加到WHERE标准中没有任何好处 - 与此时的INNER JOIN相同。

I like to move those types of WHERE criteria to the JOIN itself:

我喜欢将这些类型的WHERE标准移动到JOIN本身:

SELECT 
    s.jobID, s.jobNumber, s.jobType, s.countType, s.countID, s.salesRep,           s.dealershipName, s.checkTS, s.paymentID, s.paymentAmount, s.estimatedMailArrival, 
    jt1.completedTimestamp as Art, 
    jt2.completedTimestamp as List
FROM salesboard s
LEFT JOIN jobtasks jt1 ON s.jobID = jt1.jobID 
    AND jt1.taskID = 22
LEFT JOIN jobtasks jt2 ON s.jobID = jt2.jobID
    AND jt2.taskID = 23
WHERE
    s.checkTS BETWEEN '2013-03-01 00:00:00' AND '2013-03-31 23:59:59' AND
ORDER BY s.checkTS DESC;

#2

Change to:

WHERE
    s.checkTS BETWEEN '2013-03-01 00:00:00' AND '2013-03-31 23:59:59' AND
    (jt1.taskID = 22 OR jt1.taskID IS NULL) AND
    (jt2.taskID = 23 OR jt2.tastID IS NULL)

Any WHERE conditions on LEFT JOINed tables are still counted (and the values of these columns will be NULL if the table isn't joined).

LEFT JOINed表上的任何WHERE条件仍被计算(如果表未连接,则这些列的值将为NULL)。

#1