SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    forum sorting by last reply posted with two tables

    user
    forum
    subforum

    discussions are tied to one specific projects so we don't need categories

    user {userid, username, password, displayname, active, email, deleted}
    forum{forumid, projectid, userid, title, body, createdate, deleted, admincomment}
    subforum {subforumid, forumid, userid, projectid, body, createdate, deleted}


    forum(topic) was getting displayed with latest forum at the top. when user clicked on forum title it opens new page with forum title on top and subforums(replies) under it.

    on main page we displaying forum as table
    topic|created by and datetime of creation| replies | last reply

    i wanted to dsiplay topic sorted according to the last reply posted but faced aproblem

    problem is that when i join 3 tables main forum with no replies did not get displayed but it get sorted by last reply posted
    sql
    SELECT f.forumid, f.userid, f.title, f.body, u.displayname, f.createdate, s.createdate as replydate FROM forum f INNER JOIN users u ON f.userid = u.userid join (select * from subforum where deleted=0 order by createdate desc) as s on s.forumid=f.forumid WHERE f.projectid = 220 AND f.deleted=0 and s.deleted =0 group by f.forumid ORDER BY replydate desc;

    problem is with database design. i have always seen posts as one table and reply to posts in same table.

    database was designed like this before i joined the project.
    is there a way to achieve the result i wanted with this design?




    thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT f.forumid
         , f.userid
         , f.title
         , f.body
         , u.displayname
         , f.createdate
         , COALESCE(s.createdate,f.createdate) AS replydate 
      FROM forum AS f 
    INNER 
      JOIN users AS u 
        ON u.userid = f.userid 
    LEFT OUTER
      JOIN subforum AS s
        ON s.forumid = f.forumid
       AND s.deleted = 0 
     WHERE f.projectid = 220 
       AND f.deleted=0 
    ORDER 
        BY replydate DESC;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2009
    Posts
    32
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much.

    you saved my day. learned something new.

    i think i should give another good reading to joins.
    this is the first time i came across COALESCE also.



    thanks again.


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
  •