Problem with 2 LEFT JOINS and GROUP BY
Hi, I have a problem when I fetch data from 3 different tables:
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:
From this query I'm getting wrong values, it seems that myValue1 is multiplied by myValue2...
SUM(childTable1.childTable1Field1) + SUM(childTable1.childTable1Field2) AS myValue1,
COUNT(childTable2.id) AS myValue2
LEFT JOIN childTable1 on childTable1.mainTableId = mainTable.id
LEFT JOIN childTable2 on childTable2.mainTableId = mainTable.id
GROUP BY mainTable.id
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