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

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

what select specific set of rows 1 table (table a) , bring together table (table b), such 1 record appear table a, joined recent record table b, based on datetime column.

for example, table has construction (heavily simplified):

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

and table b looks 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 result set looks this:

id | col_1 | col_2 | datetime_col | col_3 ---+-----------+----------------+---------------------+-------- 1 | | 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 can see table b has been joined table on b.fk_a = a.id, recent corresponding record b has been included in results.

i have tried various combinations of select distinct, left join , sub-queries , can't work, either no results or this:

id | col_1 | col_2 | datetime_col | col_3 ---+-----------+----------------+---------------------+-------- 1 | | else | 2012-02-01 15:42:14 | note 1 1 | | else | 2012-02-02 09:46:54 | note 2 1 | | 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 records table repeated.

obviously sql-fu not plenty task, grateful if 1 of kind people point me in right direction. have done quite bit of googling , searching around , have not found matches specific task, although sure question has been asked before - suspect there sql keyword forgetting/unaware of , if searched find reply instantly.

i think this question deals same problem although not 100% sure , accepted reply involves select top, thought (?) not valid in mysql.

as actual query much more complicated , joins several tables, shall show in case makes difference how done:

select `l` . * , `u`.`name` 'owner_name', `s`.`name` 'acquired_by_name', `d`.`type` `dtype` , `p`.`type` `ptype` `leads` l left bring together `web_users` u on `u`.`id` = `l`.`owner` left bring together `web_users` s on `s`.`id` = `l`.`acquired_by` left bring together `deal_types` d on `d`.`id` = `l`.`deal_type` left bring together `property_types` p on `p`.`id` = `l`.`property_type`

this query works , returns info want (sometimes add together where clause works fine), to:

left bring together `notes` n on `n`.`lead_id` = `l`.`id`

...where notes contains "many records" , leads contains "one record" relate to.

it should noted potentially want homecoming oldest record (in different query) imagine simple case of inverting asc/desc somewhere, or easy.

i think help you:

select a.id, a.col_1, a.col_2, a.datetime_col, a.col_3 (select b.id, b.col_1, b.col_2, c.datetime_col, c.col_3 tablea b left outer bring together tableb c on b.id = c.id order c.datetime_col desc) grouping a.id

mysql join one-to-many