November 18th, 2003, 02:26 AM
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?
November 18th, 2003, 02:33 AM
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)
November 18th, 2003, 01:14 PM
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