Limit JOIN to 1 result (latest) MySQL

I imagine I have done something daft here but I just can't see it.

I have 2 tables I am trying to join in a query. One contains incidents and the other audit data for showing when an incident is updated. I would like to be able to select the data in the incidents table, and a timestamp for the latest audit entry; so I can show a 'last updated' column.

See abridged tables below:

incedententry:

incidentaudit:

This is the query I have created. I have attempted to JOIN the incidentaudit table using a subquery with a limit of 1. This does not seem to work though. The returned result has a row for each audit entry...

Query:

SELECT e.*,a.* FROM `incedententry` as `e` INNER JOIN `incidenttypes` ON `e`.`IncidentType` = `incidenttypes`.`IncidentTypeID` LEFT JOIN `incidentaudit` AS `a` ON `e`.`IncidentRef` = ( SELECT `IncidentRef` FROM `incidentaudit` AS `a2` WHERE `a2`.`IncidentRef` = `e`.`IncidentRef` ORDER BY `a`.`TimeStamp` DESC LIMIT 1)

Result

The desired result would be:

Thank you in advance for any assistance!

Solution 1:

SELECT e.*, a.* 
FROM incedententry as e 
INNER JOIN incidenttypes 
  ON e.IncidentType = incidenttypes.IncidentTypeID 
LEFT JOIN (
  SELECT MAX(Timestamp) AS Timestamp, IncidentRef 
  FROM incidentaudit 
  GROUP BY IncidentRef -- you were missing this
) AS a 
  ON e.IncidentRef = a.IncidentRef;