title: SQL SELECT data between two related tables NOT within a certain date range
body: <p>I have 2 many to many tables, and a table to join them.</p>
  
  <p><strong>Officer</strong></p>
  
  <ul>
  <li>id</li>
  <li>name</li>
  </ul>
  
  <p><strong>Report</strong></p>
  
  <ul>
  <li>id</li>
  <li>performanceDate</li>
  <li>title</li>
  </ul>
  
  <p><strong>report_officer</strong></p>
  
  <ul>
  <li>officer_id</li>
  <li>report_id</li>
  </ul>
  
  <p><strong>I want to select all officers that haven't ever been associated with a report or who has not been associated with a report within a certain timeframe.</strong></p>
  
  <p>So far I have tried the following (below doesn't work for me!):</p>
  
  <pre><code>SELECT * 
    FROM Officer 
         LEFT JOIN report_officer 
              ON Officer.id = report_officer.officer_id 
         LEFT JOIN Report 
              ON Report.id = report_officer.report_id
   WHERE (performanceDate IS NULL 
          OR performanceDate &lt; "2014-03-23 00:00:00" 
          OR performanceDate &gt; "2014-04-01 00:00:00"
          )
  </code></pre>
  
  <p>My left join query works only when the officer has ONLY been associated with a report within a certain timeframe, but fails once they have multiple reports.</p>
  
  <p>Result:</p>
  
  <pre><code>+------------+-----------------+
  | officer_id | performanceDate |
  +------------+-----------------+
  |        130 | NULL            | # good
  
  |        134 | 2014-03-02      | # bad - officer_id 134 has a performanceDate  
  |        134 | 2014-03-09      | # on 2014-3-30, I do not want this in the results.
  |        134 | 2014-03-16      | # 
  
  |        135 | 2014-03-02      | # good
  +------------+-----------------+
  </code></pre>
  
  <p><strong>SQL Fiddle:</strong> <a href="http://sqlfiddle.com/#!2/1bf72/3" rel="nofollow">http://sqlfiddle.com/#!2/1bf72/3</a> &lt;- in the sql fiddle, please refer to the 'name' field for which columns I am looking to have returned.</p>
  
  <p>Any ideas on how to make this work?</p>
  
  <p>Ideally I would like to make this as simple as possible to work with my ORM. I am using doctrine and would prefer not to start using totally custom code (so if it can be done with only joins, that would be great). I though have a bad feeling I need a sub-query.</p>