LEFT JOIN is not executed individually

[Vebut]

Hello!

I'm trying to calculate a total sum from 3 different tables while grouping by a fourth, but my result vary and is not very accurate in some cases. I'm hoping for some help with understanding whats causing this problem.

 

I've got 3 tables collecting data, 2 of these has a relation and the third is a standalone. This query will return a total that is many times too high for the first result, but the following 9 results are correct. The thing is that its only the first result for this specific table4 reference (1) that has results in table3. So I'm guessing the GROUP clause might be the cause of this problem.

 

table1 is referenced by table5.id

table2 is referenced by table1.id

table3 is referenced by table5.id

table4 is referenced by a given value

table5 is referenced by table4.id

 

			SELECT
			DISTINCT SUM(
				IF(ISNULL(table1.id), 0, (table1.col1 + table1.col2) * table1.col3)
				+
				IF(ISNULL(table2.id), 0, table2.col1 * table2.col2)
				+
				IF(ISNULL(table3.id), 0, (table3.col1 - table3.col2) * table3.col3)
			) AS total,
			table4.name AS name
		FROM table4
			INNER JOIN table5 ON table5.reference = table4.id

			LEFT JOIN table1 ON table1.reference = table5.id AND YEAR(table1.timedate) = 2011
			LEFT JOIN table2 ON table2.reference = table1.id
			LEFT JOIN table3 ON table3.reference = table5.id AND YEAR(table3.timedate) = 2011 
		WHERE
			table4.reference = 1
		GROUP BY table4.id
		ORDER BY total DESC
		LIMIT 10

[Muddy_Funster]

Without going into why you have NULL values in an ID field, could you post up your actual table structures and sample data sets?

[Vebut]

Shouldn't a LEFT JOIN result as NULL if there are no rows? Is there something that you think might cause my problem in the query above? The query is identically to the "real deal" except for the names and columns.

 

I'll post complete structure later if it is necessary with sample data.

[Muddy_Funster]

yeah, gonna need to see the structure & data, It looks to me like subqueries might be the way to go for this one instead of strate joins.

[fenway]

Sorry, what?

[Vebut]

Sorry, what?

 

Please elaborate.

[fenway]

Your example is too generic - I don't understand anything from "table1" and "col1".