How to write this challenging SQL (MySQL) command?

Question

This is the scenario: I am developing a website which is similar to stackoverflow.com. After an asker/seeker posts his question, other users can post their answers to the question. A user can post more than one answer to the same question, but usually only the latest answer will be displayed. User can give comments on an answer, if comments are beyond consideration, the SQL statement is

mysql_query("SELECT * , COUNT( memberid ) AS num_revisions
FROM (
SELECT *
FROM answers
WHERE questionid ='$questionid'
ORDER BY create_moment DESC
) AS dyn_table JOIN users
USING ( memberid )
GROUP BY memberid order by answerid asc")or die(mysql_error());

When comments are taken into considerations,there will be three tables. I want to select all the latest answer a solver gave on a particular question, how many answers(num_revisions) a solver gave on the question, the name of the solver,the comments on these latest answer. How to write this SQL statement? I am using MySQL.

I hope you can understand my question. If you are not clear about my question, just ask for clarification.

It is a little bit complex than stackoverflow.com. On stackoverflow.com, you can only give one answer to a question. But on my website, a user can answer a question many times.But only the latest answer will be seriously treated.

The columns of comment table are commentid, answerid,comment, giver, comment_time. So it is question--->answer---->comment.

Answer 1

You can use a correlated subquery so that you only get the latest answer per member. Here's T-SQL that works like your example (only answers for a given question). And you'll have to convert to mysql flavour:

select *
from answers a
where questionid = '$questionid'
    and answerid in (select top 1 answerid 
      from answers a2 
      where a2.questionid = a.questionid 
       and a2.memberid = a.memberid 
      order by create_moment desc)
order by create_moment

You haven't provided the schema for your comments table so I can't yet include that :)

-Krip

Answer 2

How about this (obviously answers will repeat if there is more than one comment):

select * from answers a left outer join comment c on c.answerid = a.answerid join users u on u.memberid = a.memberid where questionid = 1 and a.answerid in (select top 1 answerid from answers a2 where a2.questionid = a.questionid and a2.memberid = a.memberid order by create_moment desc) order by a.create_moment, c.comment_time

-Krip