CODEWITHSUNDEEP
sqlsql-serverdatabase
Laximas
Laximas

Reputation: 33

SQL: Select groups that do not contain a certain value

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!

Upvotes: 3

Views: 1737

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I think your query is giving any employee that is certified on a non-Boeing aircraft -- a subtly different set of people.

For your question, I would go for not exists:

select e.*
from employees e
where not exists (select 1
                  from certified c join
                       aircrafts a
                       on c.aid = a.aid
                  where e.empid = c.empid and a.name like '%BOEING%'
                 );

Another approach -- if you just want the employee id -- uses aggregation and having

select e.empid, e.firstname
from employees e join
     certified c
     on e.empid = c.empid join
     aircrafts a
     on c.aid = a.aid
group by e.empid, e.firstname
having sum(case when a.name like '%BOEING%' then 1 else 0 end) = 0;

I happen to like this method, because it generalizes very easily to other conditions -- such as flies Boeing but not Airbus or flies Learjet and Cessna.

Upvotes: 3

DxTx
DxTx

Reputation: 3357

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      |
+-------+-----------+

Upvotes: 4

ScaisEdge
ScaisEdge

Reputation: 133360

You should use NOT IN the emp that are certified by Boeing in join with aircrafts

SELECT DISTINCT FIRSTNAME
FROM EMPLOYEES
WHERE EMPID NOT IN (SELECT EMPID
                FROM CERTIFIED c 
                INNER JOIN AIRCRAFTS a ON on a.AID  = c.AID 
                WHERE a.NAME LIKE 'BOEING%')

Upvotes: 1

Related Questions