>I have a special request to get a list of records, where there is a bunch of INNER JOIN, but in one specific INNER JOIN, I need to get the most recent record. As far as I know, there is no way to obtain that directly from a INNER JOIN syntax. My assumption is that I would have to go sub query for this one. Any comment?

Check these blogs on this topic
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2

that explain many possible solutions in details and with performance tests (second blog).