CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DatsunBing
DatsunBing

Reputation: 9076

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...

Upvotes: 2

Views: 207

Answers (2)

clyc
clyc

Reputation: 2450

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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

Upvotes: 5

Related Questions