I have 2 tables, Car_Table and History_Table. I want to be able to select the last history item for all cars when their status is set to broken.
The tables are as follows
Car_Table
Car_ID Driver_Name Car_Status
1 Alan Broken
2 Dave Broken
and
History_Table
id Date Notes Car_Id
1 01-01-2017 Change oil 1
2 02-01-2017 Check Brakes 1
3 02-01-2017 Service 2
3 03-01-2017 Cleaning 2
When I do
select Car_Table.Driver_Name,History_Table.Notes from Car_Table
inner join History_Table on Car_Table.Car_ID = History_Table.CarID
where Car_Table.Car_Status = 'Broken'
I get all of the history records returned. Is there a way to get the last history item for each car that has a status of "broken" ?
You can find row with max date for each car using the left join technique and then join it with the Car_table:
select Car_Table.Driver_Name,
History_Table.Notes
from Car_Table
inner join (
select h1.*
from History_Table h1
left join History_Table h2 on h1.Car_ID = h2.Car_ID
and h1.date < h2.date
where h2.Car_ID is null
) History_Table on Car_Table.Car_ID = History_Table.CarID
where Car_Table.Car_Status = 'Broken';
See official MySQL site for alternate solutions (using aggregation in correlated and uncorrelated subquery, which are likely to be slower than the solution above).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments