SitePoint Sponsor

User Tag List

Results 1 to 12 of 12

Threaded View

  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    0 Post(s)
    0 Thread(s)

    How to select 3 topic and list the most recent comment

    I been banging my head against the wall. I'm not entirely sure what i am doing wrong here...

    I hope some SQL guru is out there and willing to help.

    I have two tables. Discussion_Topic and Discussion_Comment.
    There is a dtop_id value in the 'discussion_topic' table that matches a column in 'discussion_comment' called 'dcom_topic_id'. (that allows for selecting comments that belong to the topic with matching IDs. Pretty simple.)

    I made a new column in discussion_topic called 'rank'. There will be 3max listed and they are labeled 1,2,3 and in that order too. So i can grab 3 topic titles and output them to the page. (not having a problem with this)

    Next I want to grab the most recent comment from the comments table and output the comment just below each topic title. And Here lies the issue. The results always show the oldest comment.

    Here is the SQL statement i am using:

    SELECT * FROM discussion_topic, discussion_comment
    WHERE discussion_topic.dtop_id = discussion_comment.dcom_topic_id
    AND discussion_topic.rank > 0
    AND discussion_topic.dtop_active = 1
    GROUP BY discussion_topic.dtop_id
    ORDER BY discussion_topic.jtcrank ASC
    I think this is probably just something simple that i am doing wrong.

    thanks in advance!
    Last edited by RobbieGoD; Jan 27, 2010 at 14:48. Reason: wanted to add more info to make it more clear.

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts