Assuming I have two tables, "users" and "audit", and the audit table is populated by a trigger each time the user's status changes. I'm trying to do a select from both tables, and get the user entry (unique) with the associated latest audit entry. Say the tables have the following fields:
- id (just an arbitrary autoincrement)
- user_id (fk)
- created_at (date/time when this happened)
Thus, for each user entry, there's multiple audit entries.
So, eventually I need the result to be say (where the italic part is the results from audit, and the non-italic is the user part of the row in the mysql result-set):
1, john, firstname.lastname@example.org, 10, 5, 1, 10, 2012-08-10-10:15:59
Now, the only way I can see is to do compounded selects, where I do something like: SELECT U.*, (SELECT * FROM audit A2 where A2.id = max(A.id)) FROM users U left join audit A on (U.id = A.user_id);
Problem is, if you're having thousands or perhaps hundreds of thousands of results in user, you'll be doing n+1 queries internally in the DB - which is really inefficient.
Any ideas on how to write this as one SQL statement without using compounded SELECTS?