TSQL Search 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 names of the pilots who are trained for both the A1 Fighter and the B1-Fighter.

I got the result by doing

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

Are there any alternatives for finding rows in another 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

, , 1-, B1-Fighter.

, SouthOperation :

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

" , SouthOperation", : SouthOperation? ?

Answer 4

The question you posted almost exactly matches the examples in this article by Joe Selco: Separated We stand: SQL Relational Division , and indeed, it covers the fact that the “right” term for this is relational division.

Answer 5

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