Help with SQL to search for planes that have already done all the routes

Navigation
4

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)
    
asked by anonymous 25.11.2015 / 21:36

1 answer

2

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 )
25.11.2015 / 23:30
Post IdHTTP FIREMONKEY Delphi XE7 TempData Doubt C # with MVC