2

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?

Parfait
  • 104,375
  • 17
  • 94
  • 125
brinch
  • 2,544
  • 7
  • 33
  • 55
  • @Barmar: I think you are a little to fast with the "duplicate button". I don't believe this is a duplicate of the question you linked to. When we spend time typing in questions on stackoverflow, you could at least read the questions thoroughly before you mark them as duplicate! – brinch Sep 01 '17 at 23:25
  • I think it's a duplicate. Just move `c.created = (...)` from the `WHERE` clause to the `ON` clause and it should work. – Barmar Sep 01 '17 at 23:27
  • Also see [this question](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1). It shows how to write a query that gets the newest comment for each question, you can left join with that query. – Barmar Sep 01 '17 at 23:29
  • @Barmar: but although the solutions might be similar, the question is still not a duplicate. – brinch Sep 01 '17 at 23:30
  • @Barmar: But anyway thanks for your help. – brinch Sep 01 '17 at 23:31
  • What's the difference? They're both about trying to access a column in the left joined table in the `WHERE` clause, which causes the rows without a match to be removed from the result. – Barmar Sep 01 '17 at 23:31
  • Questions are rarely EXACT duplicates. The problem is the same, the solution is the same. – Barmar Sep 01 '17 at 23:32

1 Answers1

1

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
LAS
  • 829
  • 5
  • 7