0

Short setup

enter image description here

consider the following.

SELECT forum_category.groupid, 
       forum_category.categoryid, 
       forum_category.categoryname, 
       forum_category.categorydescription, 
       forum_category.category_url, 
       forum_category.accesslevel ,
COUNT(DISTINCT forum_topic.topicid) AS topics , 
COUNT(DISTINCT forum_post.postid) AS posts 
FROM forum_category 
INNER JOIN forum_topic ON forum_topic.categoryid=forum_category.categoryid 
INNER JOIN forum_post ON forum_post.topicid=forum_topic.topicid 
WHERE groupid = 1

result

enter image description here

This gives me actually one result, while i expect multiple rows (in this case 2) to come back. What am I missing here?

Dorvalla
  • 5,027
  • 4
  • 28
  • 45
  • Use aggregate function without group by clause..that's why you get one row – Rahul Biswas Oct 23 '21 at 07:52
  • With just thoses informations, i’d say this is because the `INNER JOIN` are filtering the other result, but I cannot be sure since I don’t know what’s in the `forum_topic` and `forum_post` tables. – Joyescat Oct 23 '21 at 07:58
  • Hi, could you elaborate on that? I tried to use the group by `groupid`, but wont buldge. – Dorvalla Oct 23 '21 at 07:58
  • please provide table structure with sample data – Rahul Biswas Oct 23 '21 at 08:00
  • @Joyescat I updated the question with the neccessairy tables – Dorvalla Oct 23 '21 at 08:03
  • Basically, you get strange results because your query is invalid. MySQL has a special mode that allows that query anyway. Please [enable the `only_full_group_by` -mode](https://stackoverflow.com/q/55674941) and check [What are the benefits of only_full_group_by mode?](https://stackoverflow.com/q/45484068). – Solarflare Oct 23 '21 at 10:10
  • 1
    About your problem, Rahul mentioned it in the first comment: `count()` will group your results to one row (same as, in your case, `group by groupid`: one row per groupid, you have one groupid, so you are expected to get one row). Please show the expected result for us to understand what you want (maybe try `group by forum_category.categoryid`?). – Solarflare Oct 23 '21 at 10:11
  • @Solarflare Thanks, that was the direction i needed. Had no idea i had to use `group by` in order to get this right. Indeed if use the `categoryid` for grouping i get the result i needed. – Dorvalla Oct 23 '21 at 10:53

0 Answers0