MySQL LEFT JOIN only 1 row depending on MAX() value

Question

Table 1

ID | Name
1    Brain
2    Amy

Table 2:

ID | WorkDay    | MissionCode
1    2019-01-01    2360
1    2019-02-01    2470
2    2019-01-01    4470
2    2019-02-01    7210

What I want to achieve is, I want to get all table 1 fields and left join on the table 2 but only the latest WorkDay values, like this:

Expected Result

ID | Name | WorkDay    | MissionCode
1    Brain  2019-02-01   2470
2    Amy    2019-02-01   7210

What I've tried so far is:

SELECT 
    table1.*, t2.WorkDay, t2.MissionCode
FROM 
    table1
LEFT JOIN
    (SELECT
        *
     FROM
        table2
     ORDER BY
        WorkDay DESC
     LIMIT 0,1) AS t2
ON
   t2.id = table1.id

But it returns NULL values from table2, like this:

ID | Name | WorkDay    | MissionCode
1    Brain  NULL         NULL
2    Amy    NULL         NULL

I tested the same query adding extra WHERE clause to the inner join's select command and it succedeed.

SELECT 
    table1.*, t2.WorkDay, t2.MissionCode
FROM 
    table1
LEFT JOIN
    (SELECT
        *
     FROM
        table2
     **WHERE id = 1**
     ORDER BY
        WorkDay DESC
     LIMIT 0,1) AS t2
ON
   t2.id = table1.id

And it returns ok, for the first row ofcourse:

ID | Name | WorkDay    | MissionCode
1    Brain  2019-02-01   2470
2    Amy    NULL         NULL

But I can't use

WHERE id = table1.id 

because MySQL says

Unknown column 'table1.id' in 'where clause'

So, what is the correct way of this ?

Answer 1

You can use a simple JOIN to table2, just put the MAX(WorkDay) condition into the JOIN condition as a correlated subquery, where you can access the table1 id value:

SELECT *
FROM table1 t1
JOIN table2 t2 ON t2.id = t1.id AND
                  t2.WorkDay = (SELECT MAX(WorkDay) 
                                FROM table2 
                                WHERE table2.id = t1.id)

Output:

ID  Name    ID  WorkDay     MissionCode
1   Brain   1   2019-02-01  2470
2   Amy     2   2019-02-01  7210

Demo on dbfiddle