mysql - Select rows from one table, join most recent row from other table with one-to-many relationship

Question

What I would like to do is select a specific set of rows from one table (table A) and join with another table (table B), such that only one record will appear from table A, joined with the most recent record from table B, based on a datetime column.

For example, table A has this structure (heavily simplified):

id | col_1     | col_2          
---+-----------+----------------
1  | something | something else 
2  | val_1     | val_2
3  | stuff     | ting
4  | goats     | sheep

And table B looks like this:

id | fk_A      | datetime_col        | col_3
---+-----------+---------------------+--------
1  | 1         | 2012-02-01 15:42:14 | Note 1
2  | 1         | 2012-02-02 09:46:54 | Note 2
3  | 1         | 2011-11-14 11:18:32 | Note 3
4  | 2         | 2009-04-30 16:49:01 | Note 4
5  | 4         | 2013-06-21 15:42:14 | Note 5
6  | 4         | 2011-02-01 18:44:24 | Note 6

What I would like is a result set that looks like this:

id | col_1     | col_2          | datetime_col        | col_3
---+-----------+----------------+---------------------+--------
1  | something | something else | 2012-02-02 09:46:54 | Note 2
2  | val_1     | val_2          | 2009-04-30 16:49:01 | Note 4
3  | stuff     | ting           | NULL                | NULL
4  | goats     | sheep          | 2013-06-21 15:42:14 | Note 5

So you can see that table B has been joined with table A on B.fk_A = A.id, but only the most recent corresponding record from B has been included in the results.

I have tried various combinations of SELECT DISTINCT, LEFT JOIN and sub-queries and I just can't get it to work, I either get no results or something like this:

id | col_1     | col_2          | datetime_col        | col_3
---+-----------+----------------+---------------------+--------
1  | something | something else | 2012-02-01 15:42:14 | Note 1
1  | something | something else | 2012-02-02 09:46:54 | Note 2
1  | something | something else | 2011-11-14 11:18:32 | Note 3
2  | val_1     | val_2          | 2009-04-30 16:49:01 | Note 4
3  | stuff     | ting           | NULL                | NULL
4  | goats     | sheep          | 2013-06-21 15:42:14 | Note 5
4  | goats     | sheep          | 2011-02-01 18:44:24 | Note 6

...with the records from table A repeated.

Obviously my SQL-fu is just not good enough for this task, so I would be most grateful if one of you kind people could point me in the right direction. I have done quite a bit of Googling and searching around SO and I have not found anything that matches this specific task, although I am sure the question has been asked before - I suspect there is an SQL keyword that I am forgetting/unaware of and if I searched for that I would find the answer instantly.

I think this question deals with the same problem although I am not 100% sure and the accepted answer involves SELECT TOP, which I thought (?) was not valid in MySQL.

As my actual query is much more complicated and joins several tables, I shall show it in case it makes any difference to how this is done:

SELECT  `l` . * ,  `u`.`name` AS  'owner_name',  `s`.`name` AS  'acquired_by_name',  `d`.`type` AS  `dtype` ,  `p`.`type` AS  `ptype` 
FROM  `leads` l
LEFT JOIN  `web_users` u ON  `u`.`id` =  `l`.`owner` 
LEFT JOIN  `web_users` s ON  `s`.`id` =  `l`.`acquired_by` 
LEFT JOIN  `deal_types` d ON  `d`.`id` =  `l`.`deal_type` 
LEFT JOIN  `property_types` p ON  `p`.`id` =  `l`.`property_type`

This query works and returns the data I want (sometimes I also add a WHERE clause but this works fine), but I would now like to:

LEFT JOIN `notes` n ON  `n`.`lead_id` =  `l`.`id`

...where notes contains the "many records" and leads contains the "one record" they relate to.

It should also be noted that potentially I would also want to return the oldest record (in a different query) but I imagine this will be a simple case of inverting an ASC/DESC somewhere, or something similarly easy.

Answer 1

I think this will help you:

SELECT A.id, A.col_1, A.col_2, A.datetime_col, A.col_3
FROM
    (SELECT B.id, B.col_1, B.col_2, C.datetime_col, C.col_3
    FROM tableA B LEFT OUTER JOIN tableB C ON B.id = C.id
    ORDER BY C.datetime_col desc) as A
GROUP BY A.id