Question :
Imagining that there are 3 planes (A, B, C) and 5 routes and the inserts (A-1; B-2,4,5; C-1,2,3,4,5), I want to select only the planes that have already made flights in all the routes, in this case the C (C-1,2,3,4,5).
I have experimented in many ways, but SQL returns all the planes that have already made at least one route (A-1, B-2.4 and C-1,2,3,4,5).
What do I miss or am I 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))
Here 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)
Answer :
Try it like this:
select a.matricula
from aviao a
left join (
select matricula, count(distinct cod_rota) numRotas
from voo
group by matricula
) v
on v.matricula = a.matricula
where v.matricula is null -- avioes que nao fizeram qualquer rota
or v.numRotas < ( select count(distinct cod_rota) from rota) -- avioes cujo número de rotas efectuadas é diferente/menor que o número total de rotas existentes
The logic is as follows:
- The first left join determines, for each airplane, the number of different routes already made
- The query will then return the planes that have not yet made any flight
- or airplanes whose number of (distinct) routes is already different from the total number of routes (
select count(distinct cod_rota) from rota
)