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?
Jump to PostUse INNER JOIN :
http://www.w3schools.com/Sql/sql_join_inner.asp
Jump to Post@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.
Hi!
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.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.