Help with SQL to find planes that have already done all routes

Asked

Viewed 98 times

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)
sql

  • 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.

  • 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

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 aeroplane, the number of different routes already taken
  • the query will then return the planes that have not yet made any flight
  • or aeroplanes whose (separate) route numbers are different from the total number of routes ( select count(distinct cod_rota) from rota )

Browser other questions tagged sql

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