I don't think this can be done in a single query, but what about:
SELECT Reference.id1, Reference.id2, sum(Table1.number)
FROM Table1, Reference
WHERE Reference.id1=Table1.id1
GROUP BY Reference.id1
Which gives you:
+------+------+--------------------+
| id1 | id2 | sum(Table1.number) |
+------+------+--------------------+
| 1 | 1 | 60 |
| 2 | 2 | 90 |
+------+------+--------------------+
or:
SELECT Reference.id1, Reference.id2, Table2.name, sum(Table1.number)
FROM Table1, Table2, Reference
WHERE Reference.id1=Table1.id1 And Reference.id2=Table2.id2 and Table2.name='John'
GROUP BY Reference.id1
+------+------+------+--------------------+
| id1 | id2 | name | sum(Table1.number) |
+------+------+------+--------------------+
| 1 | 1 | JOhn | 60 |
+------+------+------+--------------------+
Then you'd have to do a separate UPDATE query (or queries) to get the sum into Table2.
If Table2.id2 is a foreign key, though, you wouldn't even need the Reference table at all:
select id1, sum(Table1.number) from Table1 group by id1
+------+--------------------+
| id1 | sum(Table1.number) |
+------+--------------------+
| 1 | 60 |
| 2 | 90 |
+------+--------------------+
... and then the update, separately.
-bill
C. Reeve wrote:
Hi, I have one table with an id1 column and a number column, these numbers correspond to another table with an id2 column and a name column and a total column. I also have a reference table with ties the two together (an id1 and id2 column).I want to be able to select all numbers in the first table that correspond to the name in the second column and add them together and place them in the total column of the second table, but whatever I have done has failed. Can anyone suggest a way of doing this? TIA Table1 Table2 Reference id1 number id2 name total id1 id2 1 10 1 John 60 1 1 1 20 2 Mary 90 2 2 1 30 etc. etc. 2 40 2 50 etc.
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
