[SOLVED] Select most recent record from child table

[Jonob]

Hi all,

 

Assuming I have the following structure

 

table: company

company_id

company_name

 

table: status

status_id

company_id (FK)

status_date

description

 

How do I get all the records from company, and only the single most recent record (by status_date) from status? I want to show all fields from both tables, and if there is no record in status, then it should return nulls for each field in status.

 

Thanks for any help.

[angelcool]

You will have to use a JOIN query:

SELECT * FROM compay JOIN status ON company.company_id=status.company_id

 

and to get the most recent record:

SELECT * FROM compay JOIN status ON company.company_id=status.company_id ORDER BY status_date LIMIT 1

 

Good luck.

 

NaCo

[angelcool]

Missed the DESC.

SELECT * FROM compay JOIN status ON company.company_id=status.company_id ORDER BY status_date DESC LIMIT 1

[Jonob]

Just in case anyone wants to know in the future, this is what got me the right answer:

 

SELECT c.company_id, c.company_name, t1.*
FROM company c
LEFT JOIN

(select max(status_date) as date, status_id, description, company_id
from status
group by company_id) as t1 ON c.company_id = t1.company_id