Hi Eben, all !
Eben schrieb:
I have the following tables:
table1
-------
id1
some_field
table2
-------
id
id1
score
table3
-------
id
id1
score
I then have the following query:
SELECT table1.id,SUM(table2.score) as table2_score
FROM table1, table2
WHERE table1.some_field = 'value'
AND table2.id1 = table1.id
GROUP BY table1.id
This works fine and returns each record in table1, grouped by id, with
the sum of scores from table2. However, when I do this query:
SELECT table1.id,SUM(table2.score) as table2_score, SUM(table3.score) as
table3_score
FROM table1, table2, table3
WHERE table1.some_field = 'value'
AND table2.id1 = table1.id
AND table3.id1 = table1.id
GROUP BY table1.id
The sum'd score values go crazy, reflecting #s that aren't logical. Is
the issue that table2 and table3 are identical table structures, or that
I simply don't understand how the group by is really working here...?
Any advice is appreciated,
Sounds weird, and I have no obvious explanation / don't see anything you
obviously did wrong.
Please tell us the MySQL version you are using, it might help.
Could you describe a bit more exact what you mean by "go crazy" ?
Is it just that from your data you expect different values, do you get
invalid values, or more than any sum of your values could be, or what ?
Do both summed values "go crazy", or does it still work for the table2
part ?
I propose to check that a join between table1 and table3 works
correctly, like that between table1 and table2 does.
(I would like to know whether it is a problem with table3 by itself, or
with the three-table join.)
Jörg
--
Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED]
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
