CODEWITHSUNDEEP
mysqlleft-join
Brds
Brds

Reputation: 1075

MYSQL query not returning all results

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

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:

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.

If i remove all the Art and List stuff, i get the correct number of results... 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?

Upvotes: 1

Views: 349

Answers (2)

sgeddes
sgeddes

Reputation: 62831

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.

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

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;

Upvotes: 1

Michael
Michael

Reputation: 12806

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).

Upvotes: 0

Related Questions