CODEWITHSUNDEEP
mysqlsqlselectjoingreatest-n-per-group
brinch
brinch

Reputation: 2614

In mysql, how to join with second table and get row having most recent date?

In mysql I have two tables: Question and Commentary. This is what they look like:

Question: Id, Title

Commentary: QuestionId, Content, Created (date field)

QuestionId is a foreign key to Question's Id.

So I have a 0 to many relationship between Question and Commentary. That is, a question can have 0..n commentaries.

What I would like to do, is to show all questions along with the commentary being most recent (the Created field) if any. Thus if there is no commentaries question must still show, but with null in commentary's Content field.

I believe I got it almost working in this query, except that it only retrieves the questions that have comments. As mentioned I'd like the questions that have no comments as well.

select 
    q.Id AS Id,
    q.Title AS Title,
    c.Content AS Content
from question AS q
left join commentary as c on c.QuestionId = q.Id
where
   c.Created = (
      select MAX(created)
      from commentary
      where questionid = q.Id
    )

How can I adjust the script?

Upvotes: 2

Views: 72

Answers (1)

LAS
LAS

Reputation: 819

The where clause only returns rows where the commentary row has the max create date which isn't true when there are no commentary records. You just need to include rows where there the commentary question_ID is null which should always be true when there is no commentary.

select 
    q.Id AS Id,
    q.Title AS Title,
    c.Content AS Content
from question AS q
     left join commentary as c on c.QuestionId = q.Id
where
   c.Created = (
      select MAX(created)
      from commentary
      where questionid = q.Id
   )
   or
   c.QuestionId is null

Upvotes: 1

Related Questions