Aiuto risoluzione query progett
Salve a tutti, sto avendo problemi a risolvere una query che mi è stata assegnata dal professore per un progetto universitario.
nel progetto in questione stiamo simulando la creazione di un database per gestire i piani di volo. abbiamo gia realizzato i modelli concettuali e ristrutturati, ed abbiamo anche gia realizzato il diagramma su mysql.
la query ci chiede: dati due voli di linea e rispettive rotte, si riportino il numero di zone condivise dalle rotte dei due voli.
a tal proposito, abbiamo suddiviso la zona geografica in tnati quadranti, e supposto che ogni quadrante formasse una zona, con un suo codice identificativo.
abbiamo creato, tra le altre, due tabelle in particolare per questa query:
tabella VOLO DI LINEA, contenente tutte le info per i voli, tabella che poi è stata collegata alla tabella ROTTA, per cui anche la chiave di ogni rotta è presente all'interno della tabella dei voli;
tabella ROTTA, che contiene un suo id, il numero di zone attraversate(in termini di quantità), ed i codici zona, a tal proposito, siccome una rotta puòintersecare piu zone, ad esempio nel nostro modello il volo ROMA-MILANO
attraversa una sola zona, ma il volo ROMA-LONDRA attraversa 3 zone, per cui dentro la tabella rotta ci sono piu colonne riguardanti le zone, denominate come segue: la colonna CODICE_ZONA, è NOT NULL, poichè rappresenta la zona di partenza che potrebbe coincidere con l'arrivo, poi ci sono le colonne CODICE_ZONA2, CODICE_ZONA3 ecc, che possono anche essere null.
come si ptrebbe scrivere la query in modo che riporti il conteggio delle zone condivise? c'è da fare qualche modifica alle tabelle o agli attributi delle tabelle?
riporto di seguito il codice che abbiamo creato sul qule stiamo lavorando, ovvimente cosi scritto il massimo conteggio che potrà riportare è 1, pero anche aggiungendo le uguaglianze tra tutte le possibili combinazioni dei codicizona, lavorando con or ed and, ci ha fornito sempre come risultato 1.
cosa si può fare?
SELECT COUNT(CASE WHEN A.CODICE_ZONA = B.CODICE_ZONA THEN 1 END ) AS ZONE
FROM
(SELECT
V.VOLO_ID,
V.ROTTA_ID,
R.CODICE_ZONA,
R.CODICE_ZONA_2,
R.CODICE_ZONA_3
FROM
VOLO_DI_LINEA V
JOIN ROTTA R ON V.ROTTA_ID = R.ID
HAVING V.VOLO_ID = 4) A
JOIN
(SELECT
V.VOLO_ID,
V.ROTTA_ID,
R.CODICE_ZONA,
R.CODICE_ZONA_2,
R.CODICE_ZONA_3
FROM
VOLO_DI_LINEA V
JOIN ROTTA R ON V.ROTTA_ID = R.ID
HAVING V.VOLO_ID = 2) B ON A.CODICE_ZONA = B.CODICE_ZONA