StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to write this challenging SQL (MySQL) command?
text
Body
copied!<p>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</p> <pre><code>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()); </code></pre> <p>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.</p> <p>I hope you can understand my question. If you are not clear about my question, just ask for clarification.</p> <p>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.</p> <p>The columns of comment table are commentid, answerid,comment, giver, comment_time. So it is question--->answer---->comment.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload