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:
- SUM(childTable1.childTable1Field1) + SUM(childTable1.childTable1Field2) for for each mainTable.id
- 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