Hi Joerg,
Joerg Bruehe wrote:
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.
It is MySQL 5.0.22
Could you describe a bit more exact what you mean by "go crazy" ?
An expected resultset from the first query would look something like:
id1 table2_score
----------------------
1 20
However, if I then add in the join for table3, nevermind adding in the
SUM statement in the SELECT, it becomes:
id1 table2_score
----------------------
1 1500
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 ?
The score values go much higher than they should, the numbers should be
relatively low, i.e. 20 50 etc... instead when both tables are joined
in, the numbers increase to 1210, 1540, etc...
Do both summed values "go crazy", or does it still work for the table2
part ?
Both summed values go much higher
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.)
The individual joins work fine, so if I just join table2 or table3, I
get back the expected numbers, it's only when I have both tables joined
in the statement that it becomes a problem.
thanks,
Eben
Jörg
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
