title: Mysql - Finding missing dates with multiple join tables
body: <p>I need some help writing a mysql query. I need to find all missed reports for a given date range for a specific business id / project id. </p>
  
  <p>Basically, for a given business id, I need to know the name of the project and all dates that a report is either missing or not marked as completed.</p>
  
  <p>I am using the calendar table trick (as described <a href="http://www.brianshowalter.com/calendar_tables" rel="nofollow noreferrer">here</a> and <a href="https://stackoverflow.com/questions/5522701/how-to-find-missing-rows-dates-in-a-mysql-table">here</a>) to find the missing report dates, but I am having problems joining the projects table to find the associated project / business that a report was missed for.</p>
  
  <p>I basically need a result set that will give me data similar to this:</p>
  
  <pre><code>+------------+-----------+--------------+
  | project_id | name      | missing_date |
  +------------+-----------+--------------+
  | 1          | Project 1 | 2014-01-01   |
  | 1          | Project 1 | 2014-01-03   |
  | 1          | Project 1 | 2014-01-04   |
  | 1          | Project 1 | 2014-01-07   |
  | 1          | Project 1 | 2014-01-09   |
  | 2          | Project 2 | 2014-01-02   |
  | 2          | Project 2 | 2014-01-03   |
  | 2          | Project 2 | 2014-01-04   |
  +------------+-----------+--------------+
  </code></pre>
  
  <p>Here is my schema:</p>
  
  <pre><code>projects table:
  +----------------+------------------+------+-----+-------------------+----------------+
  | Field          | Type             | Null | Key | Default           | Extra          |
  +----------------+------------------+------+-----+-------------------+----------------+
  | project_id     | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
  | business_id    | int(10) unsigned | NO   | MUL | NULL              |                |
  | name           | tinytext         | YES  |     | NULL              |                |
  +----------------+------------------+------+-----+-------------------+----------------+
  
  reports table:
  +---------------------+------------------+------+-----+-------------------+----------------+
  | Field               | Type             | Null | Key | Default           | Extra          |
  +---------------------+------------------+------+-----+-------------------+----------------+
  | report_id           | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
  | project_id          | int(10) unsigned | NO   | MUL | NULL              |                |
  | report_date         | date             | NO   | MUL | NULL              |                |
  | completed           | bit(1)           | NO   |     | b'0'              |                |
  +---------------------+------------------+------+-----+-------------------+----------------+
  
  
  calendar table:
  +--------------+-------------+------+-----+---------+-------+
  | Field        | Type        | Null | Key | Default | Extra |
  +--------------+-------------+------+-----+---------+-------+
  | dt           | date        | NO   | PRI | NULL    |       |
  | month_name   | varchar(9)  | YES  |     | NULL    |       |
  | day_name     | varchar(9)  | YES  |     | NULL    |       |
  | y            | smallint(6) | YES  |     | NULL    |       |
  | q            | tinyint(4)  | YES  |     | NULL    |       |
  | m            | tinyint(4)  | YES  |     | NULL    |       |
  | d            | tinyint(4)  | YES  |     | NULL    |       |
  | dw           | tinyint(4)  | YES  |     | NULL    |       |
  | w            | tinyint(4)  | YES  |     | NULL    |       |
  | is_weekday   | bit(1)      | YES  |     | NULL    |       |
  | is_holiday   | bit(1)      | YES  |     | NULL    |       |
  | holiday_desc | varchar(32) | YES  |     | NULL    |       |
  +--------------+-------------+------+-----+---------+-------+
  </code></pre>
  
  <p>The following query below works to return a list of incompleted reports, but I still need to fill the gaps in with the dates where there is no report record at all.</p>
  
  <pre><code>select 
      p.project_id,
      p.name,
      c.dt as missing_date,
      r.completed
  from reports r
  join projects p on (r.project_id = p.project_id)
  right join calendar c on (c.dt = r.report_date)
  where c.dt &gt;= '2014-02-01'
  and c.dt &lt;= '2014-02-10'
  -- and r.report_date is null /** THE RESULT SET IS EMPTY IF I UNCOMMENT THIS **/
  and r.completed = false
  and c.is_holiday = false
  and c.is_weekday = true
  and p.business_id = 1001
  order by p.project_id, r.report_date, c.dt;
  </code></pre>
  
  <p>Any help would be much appreciated!</p>