SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2002
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need help with an SQL query.. you'd think DISTINCT() would do this but noooo

    I've written a forum which has a topics table and a posts table and I'm trying to get five recent topics and the last post in each topic in one query.

    You can see what I have at http://www.comeplaydying.com/

    For the most part it's working, the problem I am trying to solve is that I only want each topic to show up once, but what's happening is mysql is returning the last five posts and matching them with their topics when i want the last five topics to be matched with posts.

    Here is the query I'm using for the page as it is now.

    SELECT topics.*, posts.*, forums.forumid, forums.name, users.username
    FROM topics, forums
    LEFT JOIN users ON users.userid=topics.userid
    LEFT JOIN posts ON topics.topicid=posts.parentid
    WHERE topics.topicid>0 AND forums.forumid=topics.forumid
    ORDER BY topics.timestamp DESC, posts.posted DESC
    LIMIT 0,5

    You would think, since I'm SELECTing from topics and JOINing on posts it would get topics and match posts to them but no such luck. Does anyone have any ideas for this?

  2. #2
    SitePoint Addict Ramiro S's Avatar
    Join Date
    May 2003
    Posts
    321
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try using GROUP BY with the column you dont want to be repeated.
    Quasar - Web Development - Free Avatars

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2002
    Posts
    136
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much but now I have a new problem...


    The GROUP BY not only shows topics once but the post it shows for each topic is the first post in the topic. Is there any way I can change this so it only shows the last post in the topic? I have ORDER BY posts.postid DESC but that isn't helping.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    five recent topics and the last post in each topic
    Code:
    select topics.foo
         , topics.bar
         , topics.timestamp 
         , p1.baz
         , p1.qux
         , p1.posted
         , forums.forumid
         , forums.name
         , users.username 
      from topics
    inner
      join forums 
        on topics.forumid 
         = forums.forumid
    inner
      join users 
        on topics.userid 
         = users.userid
    inner
      join posts p1
        on topics.topicid
         = p1.parentid 
    inner
      join posts p2
        on topics.topicid
         = p2.parentid 
     where topics.topicid > 0 
    group
        by topics.foo
         , topics.bar
         , topics.timestamp 
         , p1.baz
         , p1.qux
         , p1.posted
         , forums.forumid
         , forums.name
         , users.username  
    having p1.posted 
         = max(p2.posted)     
    order 
        by topics.timestamp desc
    limit 0,5
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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