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
Table1Table2Reference
id1 number id2nametotalid1id2
1101John 6011
1202Mary 9022
130etc. etc.
240
250
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
