Is this possible to achieve directly from mySQL? mySQL - SQL Dilemma

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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;