SQL (mysql) - How do I construct query to sort and limit join results?

[aceO07]

I have 2 tables. Table A stores records, table B stores updates to table A.

In table A, there would be fields: id, name.
In table B, there would be fields: a_id, name, updated_at.

Any record in table A may have any number of related records in table B.

What sql query do I need to get records from table A, along with only the latest related update record from table B? (The idea is to get the latest updated_at value from table B for the associated record.)

This query

SELECT a.*, b.updated_at from a left outer join b on a.id = b.a_id group by a.id

unfortunately does not return the latest record from table B. Even adding in an order to the query does not work. Any ideas?

 

[Ka0t1x]

Shot in the dark, can't remember if MySQL can do table definitions on the fly..

select a.*,c.updated_at
from a
 left outer join (
      select a_id, max(updated_at) updated_at from b group by a_id
 ) c on (a.id=c.a_id)
group by a.id

I think you can also put a "where b.id=a.id" in the "max table"..

 

[aceO07]

Thanks Ka0t1x! That did the trick.

There's actually no need to do the last 'group by a.id' since it's already been grouped.

 

[beginner99]

EDIT: misread the question