#### 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`

)