I'm trying to build a query with 2 tables, I've got the query working ok with 1 table that gives me the latest row (MS_id) where there are several of a certain type (MS_M_id) but I would now like to add the Maintenance table which I can't figure how to do. Anyone Help?
[size="4"]This works for a single table[/size]
select Maintenance_Status.MS_id,
Maintenance_Status.MS_M_id,
Maintenance_Status.MS_Status
from Maintenance_Status
INNER JOIN
(
select max(MS_id) MS_id,
MS_M_id, MS_Status
from Maintenance_Status
group by MS_M_id
) Z on Z.MS_M_id=Maintenance_Status.MS_M_id and Z.MS_id=Maintenance_Status.MS_id
[size="4"]But I cannot find out how to add another table and just get an error. This is my attempt at adding the Maintenance Table.[/size]
select Maintenance_Status.MS_id,
Maintenance_Status.MS_M_id,
Maintenance_Status.MS_Status
Maintenance.M_id
from Maintenance_Status,
Maintenance
INNER JOIN
(
select max(MS_id) MS_id,
MS_M_id, MS_Status,
Maintenance.M_id
from Maintenance_Status
group by MS_M_id
) res on res.MS_M_id=Maintenance_Status.MS_M_id and res.MS_id=Maintenance_Status.MS_id and res.M_id=Maintenance.M_id
where Maintenance_Status.MS_Status='Quote Obtained - Landlord Authorisation Required'