NOT IN SQL (I want to select pilots who have never made a certain route) how? [duplicate]

1

I want to select the pilots (in this case commanders or co-pilots) who have never made flights with route 12345. At the moment SQL is returning all the pilots and I want it to return only the 18200 the 25100 and the 25169. Does anyone know what I'm doing wrong?

SELECT pl.id
FROM   Piloto pl
WHERE  pl.id NOT IN (
     SELECT ( pl2.id OR pl3.id )
     FROM   Piloto pl2,Piloto pl3, Voo v, Rota r           
     WHERE  (pl2.id = v.id_comandante OR pl3.id = v.id_copiloto)                       
          AND v.cod_rota = r.cod_rota                 
          AND r.cod_rota = 12345 )

    
asked by anonymous 26.11.2015 / 16:19

1 answer

0

Pilots who made the route x

select id_copiloto
from Voo 
where 
cod_rota = 12345
union 
select  id_comandante
from Voo 
where cod_rota = 12345

Pilots not who made the route x

select *
from pilotos
where id_piloto not in (select id_copiloto
                        from Voo 
                        where cod_rota = 12345
                        union 
                        select  id_comandante
                        from Voo 
                       where cod_rota = 12345)
    
26.11.2015 / 16:35