I keep my history records in one table and my pertinent data in another. I want to grab only the files from
myfiles that currently have a given status recorded in
statuses (ie, the latest status is the one I want to filter by). I'm trying a subquery which may be a mistake, but I can't really think of another way to do it because there are many statuses for a given file in
MySQL 5.0.96, column schema on both tables involved is fewer than 15.
< 500 records in
myfiles, 68k records in
SELECT myfiles.*, (SELECT status FROM statuses WHERE statuses.itemtype=2 AND statuses.id=myfiles.id ORDER BY historyid DESC LIMIT 1) AS actualStatus FROM myfiles HAVING actualStatus=0
Here's a fiddle with the actual table schemas involved. Thank you!