A: 

I'll give you a tip: It'll involve 'select top 1 ... order by'

-- edit

in retrospect, this probably should've been a comment to his post. Oh well, I can accept the downvotes. Regardless though, I think with something like this it's nicer for the OP to learn himself with tips :) MHO. Continue the downvoting at will :)

-- edit to respond to comment

select top 1
  pages.id,
  pages.name,
  version,
  contents
from
  pages_contents
inner join
  pages on
  pages.id = pages_contents.page_id
order by
  pages_contents.version desc

May look something like that. I don't know cause I can't test it, but it may also have a group by in there. Forgive me if it doesn't work as-is.

Noon Silk
after a fast Google on "select top 1 ... order by" kind of structure it seems to be ideal in this case, as no extra query is necessary (the one in the where clause) could you give an example of how would the query look like in this case
titel
responded via edit.
Noon Silk
it seems that although your SQL seems perfectly valid, mySQL does not support the "SELECT TOP N" construct :( "LIMIT N" seems to be given as an alternative for mySQL, but I cannot figure it out how could that be implemented in my case...
titel
Please disregard me; this is just totally wrong. I don't know what I was thinking.
Noon Silk
A: 

Try:

SELECT p.id, p.name, pc.contents
FROM pages p
JOIN pages_contents pc ON p.id = pc.page_id
GROUP BY p.id
ORDER BY pc.version DESC

EDIT:

The above probably won't work. Another attempt is:

SELECT p.id, p.name, pc.contents
FROM pages p
JOIN pages_contents pc ON p.id = pc.page_id
WHERE pc.version = (SELECT MAX(pc2.version) 
                    FROM pages_contents pc2
                    WHERE pc2.page_id = p.id
                   )

However, this query has nested dependency that might make it pretty slow on large result sets

PatrikAkerstrand
It's got nothing to do with a left join ...
Noon Silk
true. Removed the left join. It could, however, be used if all rows should be returned from the pages table.
PatrikAkerstrand
+3  A: 

Well, that's tested on Postgres, so might have to tweak it for MySQL. Try:

select p.id as page_id, p.name, c.version, c.data
from page p
   inner join content c
   on p.id = c.page_id
where c.version = (select max(d.version) from content d where d.page_id = p.id)

Edit: Alternatively, you might try using views. First, we prepare a view to replace the select expression in the where clause above:

create view newest_content
as select p.id as page_id, p.name, max(c.version) as version
from page p inner join content c on p.id = c.page_id
group by p.id, p.name;

By joining the content table again, we get the associated content:

create view newest_content_data
as select p.page_id, p.name, p.version, c.data
from newest_content p inner join content c on (p.page_id = c.page_id and p.version = c.version)

And the following query will only deliver the "newest" data:

select * from newest_content_data
Dirk
Creating the 'newest content' view is a good option - it's quite likely that you'll use it in other operations.
Kirk Broadhurst
A: 
SELECT * FROM pages 
JOIN pages_content on pages.id = pages_content.page_id AND pages_content.version = (SELECT MAX(version) from pages_content where pages_content.page_id = pages.id)

You also need to have an index on pages_content.page_id and pages_content.version - the mysql query optimizer is smart enough to use that index in subquery - it will make the execution faster.

Cheers

HardQuestions
Looks same as Dirk's answer ;)
HardQuestions
A: 
select p.id,p.name,c.contents 
  from (
    select page_id,max(version) as version 
       from page_contents 
    group by page_id) as sub 
  inner join 
     page_contents c on sub.page_id=c.page_id and sub.version=c.version
  inner join pages p on c.page_id = p.id;
nos