MySQL query for summing values in another table

问题:

I have got a couple of tables with a parent child relationship. I want to apply a sum function to one column of child table and return it with all data of parent table for example.

Parent_table
ID, Date, Title

Child_table
ID, another_id, column_to_sum
//(ID is foreign key pointing to Parent_table)

Sample Data in Parent_table
1, 22-11-2010 00:00:00 , 'Some Title'
2, 13-11-2010 00:00:00 , 'Some Title 2'

Sample Data in Child_table
1, 1, 10
1, 2, 11
1, 8, 3
2, 5, 11
2, 8, 6

Output of the query should return all columns in parent_table with an additional column, that is, summing the values of column_to_sum in Child_table for each item in parent_table matched by ID.

How?

回答1:

    SELECT p.ID,
      p.Date,
      p.Title,
      SUM(c.column_to_sum) Total
    FROM Parent_Table p LEFT JOIN
       Child_Table c ON p.ID = c.ID
    GROUP BY p.ID

回答2:

This is off the top of my head, but how about

SELET p.ID,
  p.Date,
  p.Title,
  SUM(c.column_to_sum) Total
FROM Parent_Table p INNER JOIN
   Child_Table c ON p.ID = c.ID
GROUP BY p.ID,
      p.Date,
      p.Title

回答3:

This does not exactly answer the question, but might help someone since this was my solution. (I know it's missing the JOIN) And have no idea about the efficiency of this solution on a large DB.

SELECT 
    Parent_table.*,
        (SELECT 
            SUM(Child_table.column_to_sum) 
        FROM Child_table 
        WHERE Child_table.id = Parent_table.id)
FROM Parent_table