title: mysql left join - returning rows using NULL and a condition
body: <p>I have two tables: 'shift' and 'event'. One shift has many events.</p>
  
  <p>Some sample data:</p>
  
  <pre><code>---- shift ---- 
  id  - location
   1  - mcdonalds
   2  - burger king
   3  - subway
  
   --- event ---
   id - shift_id - type - note
   1  - 1        - 2    - bingo
   2  - 1        - 3    - bingo
   3  - 2        - 4    - ignore
   4  - 2        - 2    - ignore
  </code></pre>
  
  <p>An event has a type, so for example: type '4' represents an event starting a shift.</p>
  
  <p>I want to build a query that returns all shifts that DO NOT have an event record where type = 4. So in the example above, I would bring back mcdonalds.</p>
  
  <p>I've got as far as:</p>
  
  <pre><code>     SELECT 
       shift.location AS location,
       FROM shift
       LEFT JOIN event ON event.shift_id=shift.id
  </code></pre>
  
  <p>and that's where I get stuck. Obviously one can add</p>
  
  <pre><code> WHERE event.type IS NULL
  </code></pre>
  
  <p>..but how does one only return rows where the null is for type = 4 ?</p>
  
  <p>Thanks....!</p>