title: Complex Join - involving date ranges and sum
body: <p>I have two tables that I need to join... I want to join table1 and table2 on 'id' - however in table two id is not unique. I only want one value returned for table two, and this value represents the sum of a column called 'total_sold' - within a specified date range (say one month), however I want more than one date range at the same time...</p>
  
  <pre><code>SELECT ta.id, sum(tb.total_sold) as total_sold_this_week, sum(tc.total_sold) as total_sold_this_month
  FROM table_a as ta
  LEFT JOIN table_b as tb ON ta.id=tb.id AND tb.date_sold BETWEEN ADDDATE(NOW(),INTERVAL -1 WEEK) AND NOW()
  LEFT JOIN table_b as tc ON ta.id=tc.id AND tc.date_sold BETWEEN ADDDATE(NOW(),INTERVAL -1 MONTH) AND NOW()
  GROUP BY ta.id
  </code></pre>
  
  <p>this works but does not SUM the rows - only returning one row for each id... how do I get the sum from table b instead of only one row???
  Please criticise if format of question could use more work - I can rewrite and provide sample data if required - this is a trivialised version of a much larger problem.</p>
  
  <p>-Thanks</p>