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

Reply via email to