Multiple Joins in a SQL Query

There is a one-to-many between table A and B, and again between table B and C. I need to list all records in table B, along with their parent from table A, and the sum() of a particular field in C. How can I do this?

I think it is something like:

SELECT A.fldlist, 
       B.fldlist, 
       SUM(C.field)
  FROM A 
  INNER JOIN B ON A.key = b.FK 
  LEFT OUTER JOIN C on B.key = C.FK
  开发者_开发问答GROUP BY B.field

Any ideas? Much appreciated...

Close, but you'll need to group by all the non-aggregate columns in your SELECT.

SELECT A.fldlist, 
       B.fldlist, 
       SUM(C.field)
  FROM A 
  INNER JOIN B ON A.key = b.FK 
  LEFT OUTER JOIN C on B.key = C.FK
  GROUP BY A.fldlist, B.fldlist

If you want to group by all fields in A and B you can always do this

With tmp as
(
   SELECT c.FK, SUM(C.field) as field
   FROM C
   GROUP BY c.FK
)
SELECT A.*, B.*, tmp.FK, tmp.field
FROM   A 
       INNER JOIN B ON A.key = b.FK
       LEFT OUTER JOIN tmp ON b.key = tmp.FK