Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Ugh. I still can't get my head around this.

    Say I've got the following:

    SELECT forum_threads.id, forum_threads.title, forum_posts.dateposted as lastPost
    FROM forum_threads INNER JOIN forum_posts ON forum_posts.thread_id=forum_threads.id

    , which is supposed to produce a list of distinct forum thread titles, along with the last date a post was made in that thread. What it actually returns is a list detailing the thread title and post date of EVERY POST in EVERY THREAD in the forum. Bah. I know this is pretty simple database stuff but I've never been good at that particular kind of cross-referenced table visualising thing. Could someone show me where I'm going wrong?

    Ta

  2. #2
    Join Date
    Jul 2002
    Posts
    55
    Try something like...
    Code:
    SELECT forum_threads.id, forum_threads.title, Max(forum_posts.dateposted) as lastPost
    FROM forum_threads 
    INNER JOIN forum_posts ON forum_threads.id = forum_posts.id
    GROUP BY forum_threads.id, forum_threads.title
    And then get a good book on relational database theory and SQL and learn, young Padawan coder

    edit: stripped out tailing semi-colon

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Aha I think I'm beginning to understand the Power of the Group By.

    re: books - anything in particular you'd recommend? Wrox? Sams?

Posting Permissions

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