Problem with 2 LEFT JOINS and GROUP BY

[ratamaster]

Hi, I have a problem when I fetch data from 3 different tables:

mainTable
id
...

------------

childTable1
mainTableId
childTable1Field1 (int)
childTable1Field2 (int)
...

------------

childTable2
mainTableId
...

What I want to fetch are some fields from mainTable and:
1) SUM(childTable1.childTable1Field1) + SUM(childTable1.childTable1Field2) for for each mainTable.id
2) COUNT(*) number of childTable2 records for each mainTable.id

To accomplish this I wrote:

SELECT mainTable.id, 
SUM(childTable1.childTable1Field1) + SUM(childTable1.childTable1Field2) AS myValue1,
COUNT(childTable2.id) AS myValue2
FROM mainTable
LEFT JOIN childTable1 on childTable1.mainTableId = mainTable.id
LEFT JOIN childTable2 on childTable2.mainTableId = mainTable.id
GROUP BY mainTable.id

From this query I'm getting wrong values, it seems that myValue1 is multiplied by myValue2...

Do you know what's wrong here ?

ps: when I write the same query but without one of the LEFT JOINS and not selecting COUNT or SUM, the query works, but of course I need both

[r937]

Do you know what's wrong here ?

you're getting cross join effects, because for each main id, you get multiple rows from one of the child tables, and each of those is joined with multiple rows from the other child table

remove the GROUP BY and (temporarily) use SELECT * and you will see the multiplication effect on the number of matched rows

the solution is to put at least one and preferably both of your aggregation operations into a subquery

SELECT mainTable.id
     , sub1.myValue1
     , sub2.myValue2
  FROM mainTable
LEFT OUTER
  JOIN ( SELECT mainTableId
              , SUM(childTable1Field1) + 
                SUM(childTable1Field2) AS myValue1 
           FROM childTable1 
         GROUP
             BY mainTableId ) AS sub1
    ON sub1.mainTableId = mainTable.id
LEFT OUTER
  JOIN ( SELECT mainTableId
              , COUNT(*) AS myValue2
           FROM childTable2 
         GROUP
             BY mainTableId ) AS sub2
    ON sub2.mainTableId = mainTable.id

[ratamaster]

Thank you, it worked as I wanted to !