title: Select Foo object without any specific Bar events
body: <p>Consider two tables:</p>
  
  <pre><code>Foo:
    id INT,
    name VARCHAR
  
  Bar:
    id INT,
    foo_id INT REFERENCES Foo(id),
    event_type VARCHAR DEFAULT NULL,
    event_duration INT DEFAULT NULL
  </code></pre>
  
  <p>Each Foo item can have multiple Bar events. <strong>How to query for Foo items which <em>do not have any Bar events that meet either of the following conditions</em>:</strong></p>
  
  <ol>
  <li><code>event_type</code> that is not one of the following values: <strong>'miss', 'scratch', 'scrape'</strong></li>
  <li><code>event_duration</code> that is not null</li>
  </ol>
  
  <p>For instance, consider:</p>
  
  <pre><code>Foo id=1:
      event_type: hit       | event_duration: NULL
      event_type: poke      | event_duration: NULL
      event_type: capture   | event_duration: NULL
  
  Foo id=2:
      event_type: hit       | event_duration: 2
      event_type: poke      | event_duration: NULL
      event_type: capture   | event_duration: NULL
  
  Foo id=3:
      event_type: miss      | event_duration: NULL
      event_type: poke      | event_duration: NULL
      event_type: capture   | event_duration: NULL
  
  Foo id=4:
      event_type: strike    | event_duration: NULL
      event_type: hit       | event_duration: NULL
      event_type: land      | event_duration: NULL
  </code></pre>
  
  <p>Only the Foo items with <code>id=1</code> and <code>id=4</code> should be returned. The item with <code>id=2</code> should not be returned as one of it's <code>event_duration</code> is not NULL. The item with <code>id=3</code> should not be returned as one of it's <code>event_type</code> is <code>miss</code> (which is on the list of forbidden event_types).</p>
  
  <p>I have tried various ideas from <a href="https://stackoverflow.com/a/21362706/343302">this terrific answer</a>, which responds to a generalization of the situation which I had hoped to learn enough from in order to build this query. Alas, I have been unable to generalize the answer enough to resolve this issue. This is one example of an unworking query, there were quite a few other failed attempts:</p>
  
  <pre><code>SELECT
      f.name
  FROM
      Foo f JOIN Bar b ON f.id = b.foo_id
  GROUP BY
      b.event_type, b.event_duration
  HAVING
      b.event_type not in ('miss', 'scratch', 'scrape')
    AND
      b.event_duration not null
  </code></pre>
  
  <p>Here is another unworking query:</p>
  
  <pre><code>SELECT
      f.name
  FROM
      (
      SELECT
          f.name, b.event_duration
      FROM
          Foo f JOIN Bar b ON f.id = b.foo_id
      GROUP BY
          b.event_type
      HAVING
          b.event_type not in ('miss', 'scratch', 'scrape')
      )
  GROUP BY
      b.event_duration
  HAVING
      b.event_duration not null
  </code></pre>
  
  <p>There were lots of other unworking queries with several ideas about JOINs and subqueries. <strong>Note that the <code>Foo</code> table has almost 5 million rows, and the <code>Bar</code> table has almost 2 million rows. The tables are indexed on the relevant fields, but <code>O(n^2)</code> are simply impossible on these large tables.</strong></p>