How to add count() as column?

[rain13]

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 November 19, 2012 by SoItBegins

[davej]

You say you have two tables and then you seem to list three tables. You also give no hint regarding your foreign keys.

[rain13]

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 November 19, 2012 by SoItBegins

[justsomeguy]

You need to group by all other columns that you are returning, not just forumID.

[rain13]

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 November 19, 2012 by SoItBegins