Again, I'm sure there's a proper name for this... errr... technique? But I have no idea what it is so you're stuck with my wobbly description. Sorry

Lets' say I've got two tables: tblFiles, and tblFileTitles

tblFiles:
id
file_title_id
url
version_number

tblFileTitles:
id
title

You get the gist: it's a file versioning system. But how do I get the latest version of each file (or rather, file TITLE)?

I tried this:

SELECT
f.id AS fileID,
MAX(f.version_number) AS latestVersion,
f.url
ft.id AS titleID,
ft.title
FROM tblFiles f
INNER JOIN tblFileTitles ft ON f.file_title_id = ft.id
GROUP BY ft.id

Which would be great except that the file ID (and URL) that it's pulling out isn't the one that corresponds to the latest version. In fact, it seems to be pulling out the first record from tblFiles that has the corresponding title_id.

Am I making sense? Can someone explain how to get the "latest" file in there?

since you asked "via join" --

Thanks - that's exactly the job I've got to ask, though - it sounds like there's a better way?

better? not necessarily
to me, it's a bit easier to understand what this query is doing, with its correlated subquery, as compared to the one with the join

next, you're going to ask which is more efficient, aren't you

the answer is: you need to test them yourself

That's it! I knew there was a trick you could do with a subquery, but could never get my head round joining it to the parent. I should really get myself some sort of theoretical grounding in this, shouldn't I? Thank you, though