Query a table with limited results, based on another table-CodePudding

Let's assume I have tbl2, with a foreign key to another table (tbl1). For each record in the tbl1, we can have multiple or no records in tbl2. I want to have only one record from tbl2 (the last record, based on time), which matches with a record on tbl1. The following query only returns one record:

select * from tbl2 where fk in (select id from tbl1 where some_criteria) order by time LIMIT 1 DESC

This query also returns all records from tbl2:

select * from tbl2 where fk in (select id from tbl1 where some_criteria) order by time DESC

I wanna have a row for each record from select id from tbl1 where some_criteria, having all details from the latest record exists in tbl2.

CodePudding user response：

You want a lateral join, available since MySQL 8.0.14:

select *
from tbl1
left outer join lateral
(
  select *
  from tbl2
  where tbl2.fk = tbl1.id
  order by time desc
  limit 1
) newest_tbl2 on true;

Here is a solution for old MySQL versions: Aggregate the tbl2 by fk to get the maximum time per fk. Then use this result in your joins.

select *
from tbl1
left outer join
(
  select fk, max(time) as max_time
  from tbl2
  group by fk
) mx on mx.fk = tbl1.id
left outer join tbl2 on tbl2.fk = mx.fk and tbl2.time = mx.max_time;