MySQL - Joining a table to itself, but keeping original results

I have the following query:

   SELECT a.`graph_value_id`, 
          SUM( a.`value` ) AS value, 
          DATE( DATE_ADD( a.`date_time`, INTERVAL 5 DAY ) ) AS date_time 
     FROM `graph_values` AS a
LEFT JOIN `graph_values` AS b ON ( a.`graph_id` = b.`graph_id` ) 
    WHERE a.`graph_id` = 921
 GROUP BY DATE( a.`date_time` ) 
   HAVING date_time >= MIN( b.`date_time` ) AND date_time <= MAX( b.`date_time` ) 
 ORDER BY a.`date_time` DESC

The table is as follows:

• graph_value_id (int)
• graph_id (int)
• value (float)
• date_time (datetime)
• date_created (datetime)
• date_updated (timestamp)

If I remove 开发者_如何学Gothe LEFT JOIN ( and all references to it ), the query's results are correct.

The problem I'm running into is that the SUM of ( a.value ) gets greatly skewed.

The only reason I have joined the second table is so that I can get the MIN and MAX dates available for the same set of data.

Does anyone know how I can make this happen?

The problem is that you are effectively creating all matching pars of rows from the two tables and summing the pairs which will give you a factor too high.

Solution: Use one derived query to perform your sum, another derived table to get the min and max, then join the two derived tables to get your result.

SELECT T1.graph_id, T1.col1, T1.col2, T2.col1, ...
FROM (
    SELECT graph_id, SUM(...) AS total
    FROM ...
    GROUP BY ...
) T1
JOIN (
    SELECT graph_id, MIN(...) AS minfoo, MAX(...) AS maxfoo
    FROM ...
    GROUP BY ...
) T2
ON T1.graph_id = T2.graph_id