Problem with MySQL query

Question

I have 2 tables:

The question table with the following structure:

id
title
userid
date

and answers table with the following structure:

id
idquestion
userid
message
date

I want to show all the questions and the latest answer to that question.

For example, if we have 5 questions, I would like to get something like this:

id    title   message   messagedate
1     qs 1    mess 1    2010-11-18
2     qs 2    mess 2    2010-11-19
3     qs 3    mess 3    2010-11-20
4     qs 4    mess 4    2010-11-21

My query so far:

    SELECT q.id, qa.id as answerid, title, qa.message 
      FROM `questions` q 
INNER JOIN questions_answers qa 
        ON q.id = qa.idquestion 
  GROUP BY q.id 
  ORDER BY q.id, answerid DESC

But it doesn't work correctly, it groups by the question id(removing all the other columns messages, leaving only the first message - so the order by is useless)

Any help appreciated. Thanks

Answer 1

Well you could approach it a different way. You are using an inner join, which means you won't show questions with no answer anyway, so search for max answer grouped by question id, and join questions table to get the title.

SELECT q.id, qa.id as answerid, q.title, qa.message FROM questions q INNER JOIN question_answers qa ON q.id = qa.idquestion AND qa.id IN (SELECT MAX(id) FROM question_answers WHERE idquestion = q.id) GROUP BY q.id ORDER BY q.id DESC

Answer 2

The old problem. Here's the solution: http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

In your case:

SELECT q.id, qa.id as answerid, title, qa.message 
FROM questions q
JOIN questions_answers qa ON q.id = qa.idquestion
LEFT JOIN questions_answers qa2 ON qa.idquestion = qa2.idquestion AND qa.date < qa2.date
WHERE qa2.idquestion IS NULL

(The idea is to split the problem in two operations: One join from questions to answers and then use the methods from the MySQL article.)