I have a table machines and a table machines_logs, I want to SELECT every machine with their latest log. The problem is that the table machines_logs has a lot of older logs that I don't want, I only need the latest one. I tried something like:
SELECT * FROM machines
RIGHT JOIN machines_logs
ON machines.id = machines_logs.machine_id
WHERE machines_logs.id = (SELECT MAX(id) FROM machines_logs);
This query returns only one value. I can't just do a JOIN because the records will be duplicated. Any tip is welcome.