'MySQL INNER JOIN possibly empty transaction table

I have 2 tables equipment with id and name transaction log with equipment_id record_type and timestamp

I need to select record from equipment based on filter and the most recent record of type 1 from transaction log if one exists.

(Some Updates) What I have so far is this:

SELECT equipment.id, equipment.name, MAX(transaction_log.timestamp) as last_update
FROM equipment
LEFT JOIN transaction_log
ON equipment.id = transaction_log.machine_id
WHERE equipment.location_id = 2 AND transaction_log.type = 1
GROUP BY machine_id;

This executes as I want as long as a transaction log record exists, if none exist I get no results. If I remove the AND transaction_log.type = 1 it works.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source