#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Australia
    Posts
    3
    Rep Power
    0

    SQL COUNT from mutiple tables


    I have a problem, I am building a forum where people create a topic/discussion this is assigned an id, then under this id they are able to post replies. My problem is I need to be able to display the total count of replies on the replies page .
    I currently have
    "SELECT * FROM (SELECT *, (SELECT COUNT (*) FROM REPLIES WHERE REP_MESSAGE = MSG_ID) AS REP_COUNT FROM MESSAGES, REPLIES)" this is listing the COUNT but for all the replies not one specific. There is a field called MSG_SUBJECT in the MESSAGES table that I want to link to this COUNT statement so I can show the COUNT for each specific topic not all topics can anyone help?
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Location
    Australia
    Posts
    3
    Rep Power
    0

    Sorry this is the actual SQL query


    SELECT * FROM (SELECT *, (SELECT COUNT (*) FROM REPLIES WHERE REP_MESSAGE = MSG_ID) AS REP_COUNT
    FROM MESSAGES, FORUMS)
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    21
    Rep Power
    0
    i'm not sure if i fully understand your question but if you know what the title of the thread is, you should be able to do the following:

    SELECT MSG_SUBJECT, COUNT (*) AS REP_COUNT
    from message m
    join replies r
    on m.msg_id = r.msg_id
    where MSG_SUBJECT = 'message subject'
    group by msg_subject

IMN logo majestic logo threadwatch logo seochat tools logo