Databases
StackOverflow2013
Postsdbo
POSQL JOIN a table and GROUP BY to get correct row
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL JOIN a table and GROUP BY to get correct row
    text
    copied!<p>Here is the situation, i for the tables forum_topics, forum_replies and users. Where the _topics contains all the topics, the _replies contains all posts and the users contain all users.</p> <p>I'm trying to list the topics as:</p> <p>(Subject)<strong><em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em></strong>(Time of last reply)</p> <p>(Topic created by username)<strong><em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__<em>_</em>__</strong>(Last reply by)</p> <p>For now, the "subject" and "topic created by username" displays just fine, however, the time of the last post and user who posted it is wrong.</p> <p>SQL:</p> <pre><code> SELECT forum_topics.id, forum_topics.category, forum_topics.subject, forum_topics.created AS topiccreate, forum_topics.createdby AS topiccreatedby, forum_replies.topic, forum_replies.created AS repliecreated, forum_replies.createdby AS repliecreatedby, usertopic.firstname AS topicfirstname, usertopic.lastname AS topiclastname, userreplie.firstname AS repliefirstname, userreplie.lastname AS replielastname, usertopic.id as topicid, userreplie.id FROM forum_topics JOIN forum_replies ON forum_replies.topic = forum_topics.id JOIN users usertopic ON forum_topics.createdby = usertopic.id JOIN users userreplie ON forum_replies.createdby = userreplie.id WHERE forum_topics.category = '1' GROUP BY forum_replies.topic ORDER BY forum_replies.created DESC </code></pre> <p>How can i get the "Time of last reply" and "Last reply by" to display correct? I've tried removing the Group By, and then it retrieves all the posts, however i just want the very latest post-data for each topic.</p> <p>As for now when using GROUP BY, it retrieves all the topics just once (correct) but the last reply by and time of the last reply is not displaying correct as it seems to retrieve the data for the first post of each topic.</p> <p>Hope you understand my question! :/</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