title: SQL: Select groups that do not contain a certain value
body: <p>I use Microsoft SQL Server Management Studio 2014 and I have these 3 tables:</p>
  
  <p>EMPLOYEES</p>
  
  <pre><code>EMPID | FIRSTNAME
    1   | JOHNNY
    2   | DWAYNE
    3   | TOM
    4   | CHRISTIAN
    5   | JACK
    6   | BRAD
    7   | ADAM
    8   | MATT
    9   | WILL
    10  | JIM
  </code></pre>
  
  <p>AIRCRAFTS</p>
  
  <pre><code>AID | NAME
   1  | BOEING 1
   2  | BOEING 2
   3  | BOEING 3
   4  | BOEING 4
   5  | AIRBUS 1
   6  | AIRBUS 2
   7  | LEARJET
   8  | DOUGLAS
   9  | JUMBO
   10 | ILYUSHIN
  </code></pre>
  
  <p>CERTIFIED</p>
  
  <pre><code>EMPID | AID
    1   |  1
    1   |  2
    1   |  3
    1   |  4
    4   |  2
    4   |  3
    7   |  1
    7   |  2
    7   |  5
    7   |  6
    8   |  7
    8   |  8
    8   |  9
    2   |  10
    2   |  1
    2   |  9
    3   |  10
    5   |  8
    5   |  9
  </code></pre>
  
  <p>The concept is that there are 10 employees and 10 aircrafts. The CERTIFIED table determines which employee is authorized to pilot which aircrafts. Not all employees are pilots though. What I need is to somehow select all pilots who are not certified to use a Boeing. What I tried but did not work is the following:</p>
  
  <pre><code>SELECT DISTINCT FIRSTNAME
  FROM EMPLOYEES
  WHERE EMPID IN (SELECT EMPID
                  FROM CERTIFIED
                  WHERE AID NOT IN (SELECT AID FROM AIRCRAFTS WHERE NAME LIKE 'BOEING%'))
  </code></pre>
  
  <p>Which gives these results:</p>
  
  <pre><code>JACK
  MATT
  TOM
  ADAM
  DWAYNE
  </code></pre>
  
  <p>This is wrong because according to the CERTIFIED table, ADAM and DWAYNE are authorized to pilot at least one Boeing. </p>
  
  <p>Any help would be appreciated, thanks in advance!</p>