Mysql – Help with this query, joining three tables in thesql

MySQL

Can you help me with this query:
I have three tables

first, main table

page_id | title
1 aaa
2 bbb
3 ccc
4 ddd

revision tbl:

id | rev_page | rev_text_id
1 1 1 
2 2 2
3 3 3
4 4 4
5 3 5
6 3 6
7 4 7

text table:

old_id | old_text
1 some_text_blob
2 some_text_blob
3 some_text_blob
4 some_text_blob
5 some_text_blob
6 some_text_blob
7 some_text_blob

I used this query

SELECT c.old_id,c.old_text 
FROM text c 
INNER JOIN revision b ON c.old_id=b.rev_text_id 
INNER JOIN page a ON a.page_id=b.rev_page 
WHERE a.page_id=3

and I get

3 some_text_blob
5 some_text_blob
6 some_text_blob

I need only the last one,
6 some_text_blob.

Best Answer

Order resulting table in the reverse chronological order and limit result to single row:

SELECT c.old_id,c.old_text 
  FROM text     AS c 
  JOIN revision AS b ON c.old_id=b.rev_text_id 
  JOIN page     AS a ON a.page_id=b.rev_page 
 WHERE a.page_id=3
 ORDER BY b.rev_text_id DESC
 LIMIT 1
;