TSQL Searching Rows in another table

Question

Two tables are given

PilotGroup Table

Pilot            Plane  

Jon Smith      A1-Fighter
Jon Smith   B1-Fighter
Gravell     A2-Fighter
Jon Skeet   A1-Fighter
Jon Skeet   B1-Fighter
Jon Skeet   A4-Fighter
Gravell     A5-Fighter

SouthOperation Table

Plane
A1-Fighter
B1-Fighter

The task is to print the pilot names who is trained on both A1-Fighter and B1-Fighter.

I got the result by executing

select distinct pilot from PilotGroup  as pg1
   where not exists
        ( 
           select * from SouthOperation Sop where 
              not exists
              ( 
                select *from PilotGroup  as pg2 where ( pg1.pilot = pg2.pilot) 
                 and 
                (pg2.plane= Sop.plane)
               )
        )

Is there any alternatives to search the rows against other table?

Answer 1

select 
    pilot 
from 
    pilotgroup pg 
    inner join
    southoperation sop on (pg.plane = sop.plane)
group by 
    pilot
having 
    count(pg.plane) = (select count(*) from southoperation)

Answer 2

SELECT Pilot FROM PilotGroup
INNER JOIN SouthOperation ON PilotGroup.Plane = SouthOperation.Plane
GROUP BY Pilot
HAVING COUNT(SouthOperation.Plane) = 2

Answer 3

The task is to print the pilot names who is trained on both A1-Fighter and B1-Fighter.

If you have these values up front, you don't need SouthOperation at all:

SELECT Pilot FROM PilotGroup WHERE Plane = 'A1-Fighter'
INTERSECT
SELECT Pilot FROM PilotGroup WHERE Plane = 'B1-Fighter'

If however you actually mean "print the names of pilots who are trained on all the planes in SouthOperation", my question to you is: are there only ever two rows in SouthOperation ? Or potentially more?

Answer 4

declare @t table (pilot int, plane int);

insert into @t
 select * from (select 1 pilot, 1 plane union all
select 1, 2 union all
select 2, 1 union all
select 3, 3 union all
select 4, 1 union all
select 4, 2) t

select t1.pilot
from @t t1, @t t2 
where t1.pilot = t2.pilot and t1.plane = 1 and t2.plane = 2
group by t1.pilot

Answer 5

The question you've posted almost exactly fits the examples in this article by Joe Celko: Divided We Stand: The SQL of Relational Division, and indeed, covers the fact that the "proper" term for this is relational division.