NOT IN SQL ( I want to select pilots who have never done a certain route) how?

Asked

Viewed 150 times

1

I want to select the pilots (in this case commanders or co-pilots) who have never flown the route 12345 . At the moment SQL is returning all pilots and I want to return only the 18200 the 25100 and the 25169 . Someone knows 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 )

inserir a descrição da imagem aqui

sql
  • 1

    Again????????

  • This time changed the voooooooooooooooo .....

  • Yeah, I haven’t been able to solve my problem yet.. Last post I was just selecting the commanders, so it’s not working, now I’m selecting both. I don’t know why I keep returning all id’s...

  • Why aren’t you doing join? That wasn’t said in the other question?

  • 3

    @Esteves, be patient with your questions, and wait for an answer that is in accordance with what you need, if it was not clear in the question click Edit and change so that it is clear, But do not keep creating a lot of question for the same subject . Make a Tour

  • I still don’t see why to open another question if the problem is still the same as the other. I’m voting to close as duplicate.

  • I did not use Jnoin because I am not allowed. Sorry, thank you

Show 2 more comments

1 answer

0

Pilots who made 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)

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.