Table1 date1 grouper1 x
Table2 date2 grouper2 y
Query is something like this:
SELECT SUM(x) as sum_x, SUM(y) as sum_y FROM Table1 LEFT OUTER JOIN Table2 on grouper2 = grouper1 (date2 >= '2003-07-01 00:00:00' AND date2 <= '2003-07-01 23:59:59' ) and year(date2) = year(date1) and month(date2) = month(date1) and DAYOFMONTH(date2) = DAYOFMONTH(date1) ) WHERE date1 >= '2003-07-01 00:00:00' AND date2 <= '2003-07-01 23:59:59' GROUP BY grouper1
Ok, so this works fine unless one of the following happens:
1. there are 2 entries in Table2 that have the same dayofmonth and grouper2 value. In this case, they are joined to the same row in Table1 so I have a duplicate of a record in Table1 and sum_x will be double for that particular day.
2. The exact opposite, so 2 entreies in table1 on the same day, i'm not too worried about this at this at the moment.
So how can I ensure to only get one of each? Distinct does not work.
Travis
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
