SQL: Select groups that do not contain a certain value

Question

I use Microsoft SQL Server Management Studio 2014 and I have these 3 tables:

EMPLOYEES

EMPID | FIRSTNAME
  1   | JOHNNY
  2   | DWAYNE
  3   | TOM
  4   | CHRISTIAN
  5   | JACK
  6   | BRAD
  7   | ADAM
  8   | MATT
  9   | WILL
  10  | JIM

AIRCRAFTS

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

CERTIFIED

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

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:

SELECT DISTINCT FIRSTNAME
FROM EMPLOYEES
WHERE EMPID IN (SELECT EMPID
                FROM CERTIFIED
                WHERE AID NOT IN (SELECT AID FROM AIRCRAFTS WHERE NAME LIKE 'BOEING%'))

Which gives these results:

JACK
MATT
TOM
ADAM
DWAYNE

This is wrong because according to the CERTIFIED table, ADAM and DWAYNE are authorized to pilot at least one Boeing.

Any help would be appreciated, thanks in advance!

Answer 1

Try this query...

SELECT employees.empid, Max(employees.firstname) AS FirstName 
FROM   certified 
       INNER JOIN employees ON employees.empid = certified.empid 
WHERE  certified.empid NOT IN (SELECT certified.empid 
                               FROM   certified 
                               INNER JOIN aircrafts ON aircrafts.aid = certified.aid 
                               WHERE  aircrafts.NAME LIKE 'BOEING%') 
GROUP  BY employees.empid 

Demo: http://www.sqlfiddle.com/#!18/8f26d/27/0

Result

+-------+-----------+
| EMPID | FirstName |
+-------+-----------+
|     3 | TOM       |
|     5 | JACK      |
|     8 | MATT      |
+-------+-----------+