MYSQL including values for dates with zero item counts

Question

I'd like to count the total number of purchases as well as the purchases by item_id over time. In this example, a user can own an item and these items can be purchased by other users. An owner can't purchase their own item.

The problem I'm having is how to return results with counts of "0" for days where there were no purchases alongside the days with positive integer counts.

Here's my tables:

      items           |          items_purchased          | numbers |   dates
i_id  item_id user_id | p_id item_id  user_id     date    |   num   | datefield
  1      1       11   |  1      1         13   2009-01-11 | 1       | 2005-06-07
  2      2       12   |  2      1         14   2009-01-11 | 2       | 2005-06-08
  3      3       11   |  3      2         15   2009-01-12 | 3       | 2005-06-09   
                      |  4      3         16   2009-01-12 | ...     | ...
                      |  5      1         17   2011-12-12 | 1000    | 2015-06-07

Here's my MYSQL query for the total numbers of purchases of user_id=11's items:

SELECT COUNT(*) as counts, DATE(purchase_date) as DATE
FROM items_purchased 
JOIN items on items_purchased.item_id=items.item_id 
WHERE items.user_id=11 
GROUP BY DATE(purchase_date)
//note this query **doesn't** make use of the numbers and dates tables b/c I don't know how to use them

Here's the results:

counts    date
  2    2009-01-11
  1    2009-01-12
  1    2011-12-12

Here's what I'd like to see instead:

counts    date
  2    2009-01-11
  1    2009-01-12
  0    2009-01-13
  0    ... // should be a row here for each day between 2009-01-13 and 2011-12-12
  1    2011-12-12
  0    ... // should be a row here for each day between 2011-12-12 and current date
  0    current date (2012-6-27)

Here's my MYSQL query for the total numbers of purchases restricted to item_id=1 which is owned by user_id=11:

SELECT COUNT(*) as counts, DATE(purchase_date) as DATE
FROM items_purchased 
JOIN items on items_purchased.item_id=items.item_id 
WHERE items.user_id=11 and items.item_id=1
GROUP BY DATE(purchase_date)

Here's the results:

counts    date
  2    2009-01-11
  1    2011-12-12

Similar to the above, here's what I'd like to see instead:

counts    date
  2    2009-01-11
  0    2009-01-12
  0    ... // should be a row here for each day between 2009-01-12 and 2011-12-12
  1    2011-12-12
  0    ... // should be a row here for each day between 2011-12-12 and current date
  0    current date (2012-6-27)

Somehow I think I need to incorporate the numbers and dates tables but I'm not sure how to do this. Any thoughts would be greatly appreciated,

thanks, tim

Answer 1

EDITED FOR CORRECTING ANSWER:

http://sqlfiddle.com/#!2/ae665/4

SELECT date_format(datefield,'%Y-%m-%d') AS DATE, IFNULL(counts, 0), item_id FROM 
    dates a
LEFT JOIN 
    (SELECT COUNT(*) as counts, purchase_date,user_id,item_id 
     FROM items_purchased 
     WHERE item_id=1
     GROUP BY date(purchase_date),item_id )r 
ON date(a.datefield) = date(r.purchase_date) ;

The above query is based on assumption:

1. Table dates contains sequential of dates that is within the range of date you want to list.
2. Not really sure what is the items table for. The second query is to group by purchase_date and item_id of items_purchased table.
3. Count is to count particular item purchased on the particular day (regardless of user_id).

UPDATE by @timpeterson (OP) Major thanks to @Sel. Here's sqlfiddles demonstrating both queries I'm interested in:

1. Purchases/day for all items owned by a single user (e.g., user_id=11): http://sqlfiddle.com/#!2/76c00/3
2. Purchases/day for item_id=1 which is owned by user_id=11: http://sqlfiddle.com/#!2/76c00/1

here's the SQL code for the 2nd one in case the link gets broken somehow:

SELECT date_format(datefield,'%Y-%m-%d') AS DATE, 
IFNULL(countItem, 0), item_id
FROM dates a
LEFT JOIN 
(SELECT countItem, purchase_date,i.user_id,p.item_id FROM (
   SELECT count(*) as countItem, purchase_date,user_id,item_id 
   FROM items_purchased 
   GROUP BY date(purchase_date),item_id
   ) p 
 inner join items i
 on i.item_id=p.item_id
 WHERE p.item_id='1' and i.user_id='11' //just get rid of "p.item_id='1'" to produce the 1st query result
)r 
ON date(a.datefield) = date(r.purchase_date);