I am trying to get forum information and count of topics and posts. I am using mysql
SELECT forums.id, forums.url, forums.title, forums.description,
forums.icon, COUNT(topics.id) topics, COUNT(posts.id) posts
FROM forums JOIN
topics
ON forums.id = topics.to_forum JOIN
posts
ON topics.id = posts.to_topic
But when there is no forum, no topic, no posts it returns
I´ve tried to use left join, right join, inner join (as I read on internet) but nothing seems to work. The left join works only if there is forum already created than it fills the data. I need it to return no record if there is not forum created no this NULL nonsence. Here are structures of the tables, there are no data in database so I dont get why it returns this
Try appending GROUP BY forums.id
to your query.
You're mixing aggregate (COUNT()
) operations with ordinary column operations in the same SELECT
. If you give any aggregate operations and you don't give GROUP BY
, you'll always get exactly one row back from the SELECT
. In standard SQL your query will fail. MySql in nonstandard: it tries to guess what you want in the non -aggregated columns. If there are no rows to count it guesses NULL
.
Read this: dev.mysql.com/doc/refman/8.0/en/group-by-handling.html
In other words, as soon as you use COUNT()
in a query you need to understand GROUP BY
even if you don't put it in your query.
Thanks that solved it