Joining 2 tables on a specific key with selecting MAX(different_key) from one of them in MySQL

Question

I am editing text, and storing the original and edited text sentence by sentence in two separate tables, like this:

(original copy)

SENTENCE
id (auto increment, primary)
url_id (refers to the URL of given copy)
sentence (longtext)

(reviewed copy)

SENTENCE_REVIEW
id (auto increment, primary)
sentence_id (this should refer to the id in the SENTENCE table)
url_id (see as before - this might be redundant here, but that's not important for now)
sentence_review (longtext)

The idea is that any given sentence can have infinite number of reviews, and the one with the highest SENTENCE_REVIEW.id is considered the final one.

What I'm trying to do is to select all_the_sentences that refer to a given url_id from the SENTENCE table while at the same time select all the "final" (as in: MAX(id)) edited sentences from the SENTENCE_REVIEW table, where the sentences were edited.

If the sentence was not edited, and the sentence_id does not exist in the SENTENCE_REVIEW table, I just want those sentences come back with those columns empty, but still come back as values from the SENTENCE table.

This last step is where I'm stuck.

I tried:

SELECT sentence.id, sentence.url_id, sentence_review.sentence_id, sentence, MAX(sentence_review.id), sentence_review FROM sentence   
LEFT OUTER JOIN sentence_review
ON sentence.id = sentence_review.sentence_id
GROUP BY sentence_review.sentence_id
ORDER BY sentence.id

Which comes back with all the "final" versions of all the edited sentences, but not the unedited ones. I tried every kind of JOIN I could think of, but to no avail.

What am I missing?

thanks,

Answer 1

You want to bring the latest review of each sentence. One option uses a left join and window functions:

select s.*, sr.sentence_review
from sentence s
left join (
    select sr.*, row_number() over(partition by sentence_id order by id desc) rn
    from sentence_review sr
) sr on sr.sentence_id = s.sentence_id and s.rn = 1

This requires MySQL 8.0 though. In earlier versions, you can use a correlated subquery for filtering:

select s.*, sr.sentence_review
from sentence s
left join sentence_review sr 
    on  sr.sentence_id = s.sentence_id
    and sr.id = (
        select max(sr1.id)
        from sentence_review sr1
        where sr1.sentence_id = sr.sentence_id
    )