"Kevin Stone" <com> wrote:
1. You're doing a LEFT JOIN, collecting all rows from gamescount but
in WHERE you throw away all the NULL rows created for rows missing
in the games table. An INNER JOIN would be more appropriate.
2. You group on the cgame column and at the same time have cgame=const
in WHERE. So GROUP BY effectively collapses all rows. You will get
the same without using GROUP BY at all.
3. You SELECT * - this alone is an offense already. But further more,
you select aggregated values and columns not in GROUP BY. The value
of all result columns except ntotal and cgame is undefined, at least
for the table(s) where cgame is not UNIQUE.
I would suggest to rewrite as subquery:
SELECT ... /* do not use *, name the columns you need */
(SELECT SUM(ncount) FROM gamescount WHERE cgame=games.cgame) AS ntotal
Note1: I assume cgame is UNIQUE across the games table.
Note2: you need MySQL 4.1 or later for the subquery.
If the data is same and the query is same, it must be a configuration
or version mismatch.
Then it is not a database problem.
Axel Schwenke, Senior Software Developer, MySQL AB
Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/