MySQL INNER JOIN possibly empty transaction table-CodePudding

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 arcade_machine.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.

CodePudding user response：

Looks like you need a LEFT JOIN with a change of your WHERE clause.

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

You also have to change your GROUP BY to make this work. I don't have a good way to test this, so hopefully it gets you close enough to nudge it into working order.