title: SQL join to get records that do not fall between two dates
body: <p>I have the following tables:</p>
  
  <pre><code>discount table:
  id
  name
  description
  amount
  
  discount_exception
  id
  from_date
  to_date
  discount_id (foreign key to discount table)
  </code></pre>
  
  <p>The discount exception table is used to store date ranges where the discount SHOULD NOT be available to the user and therefore should not be displayed.  Note that there is a 1:M relationship between discount and discount_exception.  In other words, one discount can have many exceptions.</p>
  
  <p>Right now, the way I have written the SQL is to grab all discounts, then loop through them in an array, and query the discount_exception table to find out if each discount falls within a particular date range.  I would prefer to modify the SQL so that one database call can grab all the discounts that do not have exception dates which fall within a specified date range.</p>
  
  <p>For example, if a user is purchasing a 5 day service that runs between 2013-5-1 and 2013-5-5, I would like to check the discount and discount_exception table to find out which discounts have exceptions that fall within 2013-5-1 and 2013-5-5, and then only display those discounts that DO NOT have exceptions within the specified date range.  Is there a way to do this with one select statement instead of breaking up the SQL to make a separate call to the database for each discount?  I am having a hard time getting my head around the SQL, especially when there is a 1:M relationship between the discount table and the discount_exception table.</p>
  
  <p>I was trying something along the lines of this:</p>
  
  <pre><code>SELECT * FROM discount INNER JOIN `discount_exceptions` ON discount.id = discount_exceptions.discount_id AND (discount_exceptions.date_from NOT BETWEEN '2013-5-1' AND '2013-5-5' OR discount_exception.date_to NOT BETWEEN '2013-5-1' AND '2013-5-5');
  </code></pre>
  
  <p>But this and other variations of this does not seem to be working.  Any idea what I am doing wrong?</p>
  
  <p>Thanks!</p>