4
Assuming that there are 3 aeroplanes (A,B,C) and 5 routes and the Inserts (A-1; B-2,4,5; C-1,2,3,4,5), I intend to select only the aeroplanes that have already flown on all routes, in this case C (C-1,2,3,4,5).
I have tried several ways, but SQL returns all planes that have already made at least one route (A-1, B-2,4 and C-1,2,3,4,5 ).
What I lack or am doing wrong?
SELECT a.matricula
FROM Aviao a
WHERE NOT EXISTS (
SELECT v.cod_rota
FROM Voo v
WHERE NOT EXISTS (
SELECT r.cod_rota
FROM Rota r
WHERE a.matricula = v.matricula
AND v.cod_rota = r.cod_rota))
These are my tables:
Aviao (matricula, nome, data_aquisicao, TipoAviao.cod_tipo)
Piloto (Tripulante.id, n_aterragens, n_descolagens, n_horas_voo, tipo)
Habilitado (Piloto.id, TipoAviao.cod_tipo, n_licenca, data_licenca)
Rota (cod_rota, Aeroporto.cod_aeroporto_ini, Aeroporto.cod_aeroporto_fim)
Voo (n_voo, data_partida, data_chegada, Rota.cod_rota,
Piloto.id_comandante, Piloto.id_copiloto, Aviao.matricula)
Your question is confused. You say you want to select airplanes that have flown all routes, and then you say sql selects airplanes that have flown any route. It doesn’t make sense, it’s the same thing. Edit your question and clarify better what you actually want.
– user28595
sql selects all planes that have already made at least one route, imagining that there are 5 routes, I want you to select only the planes that have already made the 5 routes in total. Thank you
– VEN