>SELECT forums.id , COUNT( forum_msg.id ) AS cnt
>FROM forums
>LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id
>ORDER BY forums.sorder ASC
Missing GROUP BY.
PB
-----
Artem Kuchin wrote:
I have two simple tables. One - list of forums, second - list of
messages and i want to
get the list of forums with number of messages in each.
Here is the query:
SELECT forums.id , COUNT( forum_msg.id ) AS cnt
FROM forums
LEFT JOIN forum_msg ON forums.id = forum_msg.forum_id
ORDER BY forums.sorder ASC
The problem is that if a forum does not have any messages then the line
with such forums.id does not appear at all.
If i delete COUNT( forum_msg.id ) AS cnt from Select - i get all forums,
as expected. If i leave the count() as shown - i get only forums with
messages
in the result. As far as i can remember it was not like this before. I
am running
5.1.3
--
Artem Kuchin
------------------------------------------------------------------------
Internal Virus Database is out of date.
Checked by AVG - http://www.avg.com
Version: 8.0.235 / Virus Database: 270.10.23/1951 - Release Date: 2/13/2009 6:51 AM
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org