MySQL MAX in Join Query

Question

I would like to join two tables on a MAX value.

The max(lg) are correct but the 'event' column wont load correctly.

Below are my example tables and desired output...

Im using...

SELECT a.id
     , a.name
     , b_lg
     , b_event
  FROM TABLE_A a
  LEFT 
  JOIN
     ( SELECT MAX(lg) as b_lg
            , event as b_event
            , enrolid as b_enrolid 
         FROM TABLE_B 
        GROUP 
           BY lg) b
    ON a.id = b_enrolid
 GROUP BY a.id

####### TABLEA ########

  id |    name        |
   1 |     John       |
   2 |     Mike       |
   3 |     Dave       |
   4 |     Sarah      |

################# TABLEB ###############

  id |  enrolid |    lg     |  event   |
   1 |  1       |    10     |    A     |
   2 |  2       |    20     |    B     |
   3 |  1       |    30     |    C     |
   4 |  2       |    60     |    D     |
   5 |  2       |    50     |    E     |
   6 |  3       |    60     |    F     |
   7 |  1       |    70     |    G     |
   8 |  3       |    20     |    H     |

###### DESIRED OUTPUT #####

  id |  name       |   lg   |   event   |
   1 |  John       |   70   |     G     |
   2 |  Mike       |   60   |     D     |
   3 |  Dave       |   60   |     F     |
   4 |  Sara       |  NULL  |   NULL    | 

Answer 1

This query will give you the results you want. It figures out the MAX value of lg for each enrolid and then uses those values to LEFT JOIN to Table_A to get the name and Table_B to get the event:

SELECT A.id, A.name, B.lg, B.event
FROM Table_A A
LEFT JOIN (SELECT enrolid, MAX(lg) AS max_lg
           FROM Table_B
           GROUP BY enrolid) M ON M.enrolid = A.id
LEFT JOIN Table_B B ON B.enrolid = M.enrolid AND B.lg = M.max_lg
ORDER BY A.id

Output:

id  name    lg      event
1   John    70      G
2   Mike    60      D
3   Dave    60      F
4   Sarah   null    null

Demo on dbfiddle.com