I have three tables: posts, topics, and forums. Each row in post table has a topic id and a author name. Each row in topics has an id that corresponds with the topic_id in posts, and then it has an id for the forum that it (the topic, containing the post) is in. Each row in the forums table has an id that corresponds with the id in topics, and then a name of the forum. I want to count the number of posts a user has in a certain forum.

How would I do that?

8 Years
Discussion Span
Last Post by verruckt24

Try this one but go through the query and apply as you need.
for any problem regarding this bug me.

select tblbcategory.bCategoryId,tblbcategory.bCategoryName,

if(COUNT(tbltopic.TopicId), COUNT(DISTINCT tbltopic.TopicId),'No Topics')
as TotalTopics,

if(COUNT(tblcomments.CommentId), COUNT(DISTINCT tblcomments.CommentId),'No Comments')
as TotalComments,

if(MAX(CONCAT(DATE_FORMAT(tblcomments.DatePosted,'%Y%c%d%h%i'), tblcomments.CommentDescription)),
SUBSTRING(MAX(CONCAT(DATE_FORMAT(tblcomments.DatePosted,'%Y%c%d%h%i'), tblcomments.CommentDescription)),12,25),'')
as description,

if(MAX(CONCAT(DATE_FORMAT(tblcomments.DatePosted,'%Y%c%d%h%i'), tblcomments.UserEmail)),
SUBSTRING(MAX(CONCAT(DATE_FORMAT(tblcomments.DatePosted,'%Y%c%d%h%i'), tblcomments.UserEmail)),12),
'No user posted last')
as LastPostedBy,

if(MAX(tblcomments.DatePosted), DATE_FORMAT(MAX(tblcomments.DatePosted),'%d %b %Y %H:%i'),'-')
as Postedon

from tblbcategory

left outer join tblscategory on tblbcategory.bCategoryId=tblscategory.bCategoryId

left outer join tbltopic on tblscategory.bCategoryId=tbltopic.bCategoryId

left outer join tblcomments on tbltopic.bCategoryId=tblcomments.bCategoryId

group by tblbcategory.bCategoryId;

@manojjena1982 : Giving away readymade stuff like that wihtout attempting to make the OP learn isn't considered a good habit here, try to correct yourself in the future.


That means u are not getting what i wrote.

I clearly mentioned that go through this query and correct it as needed.

In my sense its the correct way to learn something.


Okay so in your sense learning means giving away the wrong thing and trying to tell people to correct it ?

You could have just described what you wanted to tell him and left the writing of the query to the OP.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.