summation of second table grouped by results of first table

Question

Using MySQL syntax, how would I write a query to return the following (I am including the two table descriptions and the relationship between them):

TABLE_A (ID, DATE, TABLE_C_ID)
TABLE_B (ID, AMOUNT, TABLE_A_ID)
TABLE_C (ID)

I want to return the following, with the specified limitations:

SELECT 
    TABLE_A.ID, 
    TABLE_A.DATE 
    (SUM TABLE_B.AMOUNT 
         FROM TABLE_B 
         WHERE TABLE_B.ID = TABLE_A.ID) 
    FROM TABLE_A, TABLE_B 
    WHERE TABLE_A.TABLE_C_ID = 123

Thanks in advance.

Answer 1

What's wrong with doing it thus?:

SELECT 
    TABLE_A.ID, 
    TABLE_A.DATE,
    SUM( TABLE_B.AMOUNT ) AS AMOUNT

FROM TABLE_A

INNER JOIN TABLE_B 
ON TABLE_B.ID = TABLE_A.ID

WHERE TABLE_A.TABLE_C_ID = 123

GROUP BY TABLE_A.ID, 
    TABLE_A.DATE