Eben <[EMAIL PROTECTED]> wrote on 06/05/2008 02:44:42 PM:
> 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,
> Eben
>
Try taking away the sum and the group by and just select * from your
query. You'll see the problem is with the join, not the group by.
There are probably several solutions. Here's one way (untested). This
will only work if your version of MySQL
supports derived tables, which I think is 4.1 or higher but I'm not sure.
SELECT table1.id, t2sum.table2_score, t3sum.table3_score
FROM table1
INNER JOIN (SELECT id1, SUM(score)as table2_score FROM table2 GROUP BY
id1) as t2sum
ON table1.id=t2sum.id1
INNER JOIN (SELECT id1, SUM(score)as table3_score FROM table3 GROUP BY
id1) as t3sum
ON table1.id=t3sum.id1
Donna
