Jump to content

How to add count() as column?


rain13
 Share

Recommended Posts

Hello. I have 3 tables: forum:ForumID|col1|col2|...|colN post:ForumID|col1|col2|...|colN topicForumID|col1|col2|...|colN Now I want to get table that would look like this:forum.ForumID|forum.col1|forum.col2|...|forum.colN|<topic count>|<post count> <post count> should be something like SELECT post.COUNT(*) WHERE forum.ForumID = post.ForumID and<topic count> should be something like SELECT topic.COUNT(*) WHERE forum.ForumID = topic.ForumID If anyone wants to know: topic has 1 or more posts, forum has 0 or more topics. So far I managed to come up with following:

select forum.*,post.* FROM forum JOIN post WHERE forum.ForumID = post.ForumID

But that's far from what I want. Does anyone know how I could achieve this?

Edited by SoItBegins
Link to comment
Share on other sites

My bad. foregin key is ForumID In forum table ForumID is id of forum that is auto incremented.In post table I have ForumID which matches ForumID in forum tableIn topic table I have ForumID which matches ForumID in forum table However when I created tables I didnt use "foregin key" keyword but i guess relations mentioned above should be enough? Edit: Sofar I managed to write this: SELECT COUNT(`topic`.`ForumID`) as topics, COUNT(`post`.`ForumID`) as posts,`forum`.* FROM topic , `post` , `forum` GROUP BY forum.ForumIDwhich gives me.

topics  posts  ForumID  ParentID  ForumName  ForumType6	   6	  1	    0		 My site    1

But topics count should be 2 and posts count should be 3.

Edited by SoItBegins
Link to comment
Share on other sites

You need to group by all other columns that you are returning, not just forumID.
I figured I cant see GROUP BY effect as I only have 1 forum. I am starting to think that maybe i should use left join?I managed to come up with following. (would be interesting what you guys think of it)
SELECT forum . * , COUNT( post.ForumID ) AS posts, post.ForumID AS pfid, (SELECT COUNT(topic.TopicID) FROM topic WHERE topic.ForumID = post.ForumID) AS topicsFROM forumLEFT JOIN post ON forum.ForumID = post.ForumIDLEFT JOIN topic ON post.TopicID = topic.TopicIDLIMIT 0 , 30

Edited by SoItBegins
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...