SQL query that joins only one row from a relationship set using a derived table.

join_singlerow-only.sql

SELECT i.*, a.*
FROM inet_obdg_items i
JOIN inet_obdg_actions a ON a.item_id = i.id
JOIN (
	SELECT derived_latest.item_id, MAX(derived_latest.id) AS latest_action_id
	FROM inet_obdg_actions derived_latest
	WHERE derived_latest.deleted_at IS NULL
	GROUP BY derived_latest.item_id
) z ON z.item_id = a.item_id
	AND z.latest_action_id = a.id
WHERE a.account_id = 424