CODEWITHSUNDEEP
mysqlleft-join
Geoff
Geoff

Reputation: 256

MySQL LEFT JOIN - Empty results unless all columns included

I have had no problems with this until the new year so I assumed it was a YEARWEEK problem but I think I have confirmed that it is not.

My original statement which no longer works (returns 0 results):

SELECT t1.*, t2.family 
FROM report_tables.roc_test_results as t1 
LEFT JOIN report_tables.roc_capacity_standards as t2 
ON t1.item=t2.part_number 
WHERE YEARWEEK(t1.date_created)='201301' 
    AND t1.operation='TEST' 
    AND t1.result='Passed'

But the odd part is that this one works perfectly fine (returns 22 results):

    SELECT t1.*, t2.*
FROM report_tables.roc_test_results as t1 
LEFT JOIN report_tables.roc_capacity_standards as t2 
ON t1.item=t2.part_number 
WHERE YEARWEEK(t1.date_created)='201301' 
    AND t1.operation='TEST' 
    AND t1.result='Passed'

Does anyone know why this is no longer working? The only difference is that I include all columns from t2 on the second query

Upvotes: 0

Views: 188

Answers (1)

fthiella
fthiella

Reputation: 49049

Instead of using Yearweek(date), you should use Yearweek(date,3) that returns the year and the week according to ISO standard.

If you try this:

SELECT yearweek('2013-01-01'), yearweek('2013-01-05')
201253; 201253

you'll see that this interval is considered as being part of week 53 of 2012, and 6th of January is considered to be the first week of 2013, while according to ISO standard they are part of the first week of 2013:

SELECT yearweek('2013-01-01',3), yearweek('2013-01-06',3)
201301; 201301

I think this might be the problem. Please see also this answer to a similar problem.

Upvotes: 1

Related Questions