Professional Web Applications Themes

SQL Forum Problem - Microsoft SQL / MS SQL Server

It would be better if you provide Database Script for Tables. Easy te and work on problem. Cheers, Raghavendra Narayana   am not very good at  days now.  ForumReplies.  forum topics  list because it is  GROUP BY clause.  the query obviously  ForumTopics.authorid,  ForumTopics.dtime  ForumTopics.authorid  ForumTopics.authorid,ForumTopics.msgid,ForumTopics .title, ...

Sponsored Links
  1. #1

    Default SQL Forum Problem

    It would be better if you provide Database Script for
    Tables. Easy te and work on problem.

    Cheers,
    Raghavendra Narayana
     
    am not very good at 
    days now. 
    ForumReplies. 
    forum topics 
    list because it is 
    GROUP BY clause. 
    the query obviously 
    ForumTopics.authorid, 
    ForumTopics.dtime 
    ForumTopics.authorid 
    ForumTopics.authorid,ForumTopics.msgid,ForumTopics .title, 
    Sponsored Links
    Raghavendra Guest

  2. #2

    Default Re: SQL Forum Problem

    It helps if you include DDL (CREATE TABLE statements) and sample data
    (INSERT statements) with your posts.

    This should do the job:

    SELECT T.msgid, T.title, T.authorid,
    COALESCE(MAX(R.dtime),T.dtime) AS postdatetime,
    COALESCE(MAX(R.authorid),T.authorid) AS lastposter,
    MAX(M.replies) AS replies
    FROM ForumTopics AS T
    LEFT JOIN
    (SELECT topicid, MAX(dtime) AS dtime, COUNT(*) AS replies
    FROM ForumReplies
    GROUP BY topicid) AS M
    ON M.topicid = T.msgid
    LEFT JOIN ForumReplies AS R
    ON M.topicid = R.topicid AND M.dtime = R.dtime
    GROUP BY T.authorid, T.msgid, T.title, T.dtime
    ORDER BY postdatetime DESC

    (untested)

    Your table structure I think is questionable. A Usenet-style topic (thread)
    should be just a hierarchy of posts:

    CREATE TABLE ForumPosts (msgid INTEGER PRIMARY KEY, parent_msgid INTEGER
    NULL REFERENCES ForumPosts (msgid), subject VARCHAR(80) NOT NULL, authorid
    INTEGER NOT NULL /* REFERENCES Authors (authorid) */, postdatetime DATETIME
    NOT NULL DEFAULT CURRENT_TIMESTAMP)

    The original posts (the ForumTopics table in your schema) are just those
    where parent_msgid is NULL. This way you avoid redundant duplication of data
    across two tables.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --


    David Guest

  3. #3

    Default Re: SQL Forum Problem

    Works perfectly, thanks a million times. Have to try and understand the
    code. Never used COALESCE before..


    Karl Guest

Similar Threads

  1. Forum
    By NewBee in forum Adobe Photoshop 7, CS, CS2 & CS3
    Replies: 14
    Last Post: June 30th, 11:52 AM
  2. asp forum problem.
    By Alan Murray in forum ASP Components
    Replies: 3
    Last Post: September 8th, 02:14 AM
  3. new to this forum
    By george_s_forman@adobeforums.com in forum Adobe Photoshop Elements
    Replies: 8
    Last Post: October 21st, 01:06 AM
  4. Replies: 10
    Last Post: August 19th, 11:25 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139