MINI CORSO DI MICROSOFT ACCESS - parte 5°

QUERY A PIU' TABELLE

Si consideri l'esempio delle fatture analizzato nel primo modulo (click qui per scaricare lo script VBA che consente di ricreare l'archivio):

Immaginiamo di voler conoscere le quantità vendute per ogni singolo articolo. La seguente query:

SELECT idProdotto, Sum(Qta) AS QtaVenduta
FROM RigheFatture
Group BY idProdotto

fornisce la lista degli Id dei prodotti che sono stati effettivamente venduti con a fianco le rispettive quantità commerciate.

L’elenco ottenuto presenta due carenze:

  1. non appare la descrizione dei prodotti e pertanto risulta poco leggibile

  2. non vengono visualizzati i prodotti che sono rimasti invenduti.

La descrizione del prodotto si trova in Prodotti e quindi occorre includere nella  query originale un'ulteriore tabella. Ne consegue la necessità di un formalismo che permetta la definizione della relazione che collega i record della tabella Prodotti con quelli della tabella RigheFatture.

Per ottenere l'elenco dei nomi dei prodotti con a fianco l'ammontare delle quantità vendute (vedi figura sottostante)

devo riscrivere la query precedente  in questo modo:

SELECT Prodotti.idProdotto, DescrProdotto, Sum(Qta) AS QtaVenduta
FROM RigheFatture, Prodotti
WHERE Prodotti.idProdotto=RigheFatture.idProdotto
GROUP BY Prodotti.idProdotto, DescrProdotto;

Nel generatore delle query di Access questo comando SQL viene così visualizzato:

Osservando il comando SQL relativo a questa query notiamo:

Lo stesso risultato poteva essere ottenuto in ACCESS usando una sintassi diversa che richiede l'uso della clausola INNER JOIN:

SELECT Prodotti.idProdotto, DescrProdotto, Sum(Qta) AS QtaVenduta
FROM Prodotti INNER JOIN RigheFatture
ON Prodotti.idProdotto=RigheFatture.idProdotto
GROUP BY Prodotti.idProdotto, DescrProdotto;

 

Nel generatore delle query il comando SQL appena illustrato appare in questo modo (si noti la presenza di un collegamento!):

 

Per visualizzare i prodotti invenduti occorre avere la possibilità di estrarre dalla tabella Prodotti tutti gli articoli, indipendentemente dal fatto che siano stati venduti o meno. Tale opportunità è fornita dalla clausola LEFT JOIN (o in alternativa RIGHT JOIN).

L'elenco di tutti i prodotti (compresi gli invenduti) con a fianco le quantità commerciate può essere così riscritto:

SELECT Prodotti.idProdotto, DescrProdotto,
       Sum(iif(isnull(Qta),0,Qta)) AS QtaVenduta
FROM PRODOTTI LEFT JOIN RigheFatture
ON Prodotti.idProdotto=RigheFatture.idProdotto
GROUP BY Prodotti.idProdotto, DescrProdotto;

La sua esecuzione fornisce questo risultato:

La stessa query nel generatore delle query appare così (si noti la presenza di una freccia!):

ESEMPI:

1)  Elenco dei clienti che hanno effettuato almeno un acquisto:

SELECT Clienti.RagioneSociale
FROM Clienti, TestateFatture
WHERE (Clienti.IdCliente=TestateFatture.idCliente)
GROUP BY Clienti.RagioneSociale

oppure usando la INNER JOIN (tipica di Access):

SELECT Clienti.RagioneSociale
FROM Clienti INNER JOIN TestateFatture
ON (Clienti.IdCliente=TestateFatture.idCliente)
GROUP BY Clienti.RagioneSociale

Nel generatore di query il comando SQL viene presentato in questo modo:

2)  Elenco dei clienti che non hanno mai effettuato acquisti:

SELECT Clienti.RagioneSociale FROM Clienti
LEFT JOIN TestateFatture ON (Clienti.IdCliente=TestateFatture.idCliente)
WHERE TestateFatture.IdCliente IS null

oppure:

SELECT Clienti.RagioneSociale FROM TestateFatture
RIGHT JOIN Clienti ON (Clienti.IdCliente=TestateFatture.idCliente)
WHERE TestateFatture.IdCliente IS null

che nel generatore di query corrisponde allo schema:

3)  Elenco dei prodotti rimasti invenduti:

SELECT Prodotti.DescrProdotto FROM Prodotti 
LEFT JOIN RigheFatture ON RigheFatture.IdProdotto = Prodotti.IdProdotto
WHERE (RigheFatture.idRiga Is Null)

4)  Elenco dei prodotti che sono stati venduti almeno una volta:

SELECT Prodotti.IdProdotto, Prodotti.DescrProdotto
FROM RigheFatture, Prodotti
WHERE RigheFatture.IdProdotto = Prodotti.IdProdotto
GROUP BY Prodotti.IdProdotto, Prodotti.DescrProdotto

5) Fatturato di ogni cliente che ha effettuato almeno un acquisto:

SELECT RagioneSociale, SUM(Importo) AS Fatturato
FROM Clienti, TestateFatture
WHERE Clienti.IDCliente=TestateFatture.idCliente
GROUP BY RagioneSociale

6) Il fatturato di ogni cliente  (compreso chi non ha mai effettuato acquisti):

SELECT Clienti.RagioneSociale,
SUM(IIF(ISNULL(TestateFatture.idCliente),0,Importo)) AS Fatturato
FROM Clienti LEFT JOIN TestateFatture
ON Clienti.IdCliente = TestateFatture.idCliente
GROUP BY Clienti.RagioneSociale;

7) Numero di fatture per ogni cliente  che ha effettuato almeno un acquisto:

SELECT Clienti.RagioneSociale, Count(*) AS NrFatture
FROM Clienti , TestateFatture
WHERE Clienti.IdCliente = TestateFatture.idCliente
GROUP BY Clienti.RagioneSociale;

8) Elenco dei clienti (compreso chi non ha mai effettuato acquisti) con a fianco il numero di fatture emesse a loro carico - Si noti l'uso della funzione SUM al posto di COUNT

SELECT Clienti.RagioneSociale,
SUM(IIF(ISNULL(TestateFatture.idCliente),0,1)) AS NrFatture
FROM Clienti LEFT JOIN TestateFatture
ON Clienti.IdCliente = TestateFatture.idCliente
GROUP BY Clienti.RagioneSociale;

9) Elenco dei prodotti acquistati dalla ditta Rossi Spa con a fianco le quantità:

SELECT Prodotti.DescrProdotto, Sum(RigheFatture.Qta) AS QtaAcquistate
FROM Clienti, TestateFatture, RigheFatture, Prodotti
WHERE (Clienti.IdCliente = TestateFatture.idCliente)
AND (TestateFatture.idFattura = RigheFatture.IdFattura)
AND (RigheFatture.IdProdotto = Prodotti.IdProdotto)
AND (Clienti.RagioneSociale='ROSSI SPA')
GROUP BY Prodotti.DescrProdotto;

l'esecuzione della query restituisce l'elenco:

In alternativa si poteva anche usare la INNER JOIN (l'uso delle parentesi è indispensabile):

SELECT Prodotti.DescrProdotto, Sum(RigheFatture.Qta) AS QtaAcquistate
FROM (((
Clienti INNER JOIN TestateFatture
ON Clienti.IdCliente = TestateFatture.idCliente
)
INNER JOIN RigheFatture
ON TestateFatture.idFattura = RigheFatture.IdFattura
)
INNER JOIN Prodotti
ON RigheFatture.IdProdotto = Prodotti.IdProdotto
)
WHERE (Clienti.RagioneSociale='ROSSI SPA')
GROUP BY Prodotti.DescrProdotto;

che nel generatore di query corrisponde a questo schema

10) Elenco dei prodotti mai acquistati dalla ditta Rossi Spa:

E' necessaria una query di supporto:

SELECT Prodotti.IdProdotto
FROM Clienti, TestateFatture, RigheFatture, Prodotti
WHERE (Clienti.IdCliente = TestateFatture.idCliente)
AND (TestateFatture.idFattura = RigheFatture.IdFattura)
AND (RigheFatture.IdProdotto = Prodotti.IdProdotto)
AND (Clienti.RagioneSociale='ROSSI SPA')
GROUP BY Prodotti.IdProdotto;

che salveremo con il nome

ProdottiAcquistatiDallaRossiSPA.

La query sottostante realizza infine l'obiettivo richiesto

SELECT Prodotti.DescrProdotto
FROM Prodotti LEFT JOIN ProdottiAcquistatiDallaRossiSPA
ON ProdottiAcquistatiDallaRossiSPA.IdProdotto = Prodotti.IdProdotto
WHERE (ProdottiAcquistatiDallaRossiSPA.IdProdotto Is Null)

Utilizzando il meccanismo delle sottoquery (che vedremo prossimamente) possiamo risolvere il problema con una singola query

SELECT DescrProdotto FROM Prodotti WHERE idProdotto
NOT IN (SELECT Prodotti.IdProdotto
FROM Clienti, TestateFatture, RigheFatture, Prodotti
WHERE (Clienti.IdCliente = TestateFatture.idCliente)
AND (TestateFatture.idFattura = RigheFatture.IdFattura)
AND (RigheFatture.IdProdotto = Prodotti.IdProdotto)
AND (Clienti.RagioneSociale='ROSSI SPA')
GROUP BY Prodotti.IdProdotto)

MODELLI DI QUERY A PIU' TABELLE

Gli esempi che abbiamo appena presentato fanno riferimento a degli schemi ben precisi che adesso analizziamo in maggior dettaglio.

INNER JOIN - DIRECT JOIN

Si considerino due tabelle TabellaA e TabellaB collegate tra loro tramite il campo ChiaveA.

Il modello sintattico seguente:

SELECT TabellaA.*, TabellaB.*
FROM TabellaA, TabellaB
WHERE TabellaA.ChiaveA=TabellaB.ChiaveA

o in alternativa (solo per Access)

SELECT TabellaA.*, TabellaB.*
FROM TabellaA INNER JOIN TabellaB
ON TabellaA.ChiaveA=TabellaB.ChiaveA

è quello relativo alla DIRECT JOIN. Lo schema grafico associato a questa tipologia di join è il seguente (si osservi la linea priva di verso che collega i due campi ChiaveA):

Si osservi che il collegamento è rappresentato da una semplice linea priva di verso.

La DIRECT JOIN è utilizzata quando il nostro obiettivo è quello di estrarre i record A di TabellaA che hanno un corrispondente B (tramite il campo ChiaveA) in TabellaB.

Il risultato ottenuto da una query che utilizza la DIRECT JOIN sarà costituito da coppie di record presi da TabellaA e da TabellaB il cui campo "ChiaveA" assume il medesimo valore. I record di TabellaA che non hanno un record collegato (mediante il campo "ChiaveA") nella TabellaB saranno esclusi dalla selezione e viceversa. I record di TabellaA che hanno più record associati in TabellaB verranno ripetuti (durante l'esecuzione della query) un numero di volte pari al numero di record collegati B di TabellaB (si veda l'esempio successivo delle interrogazioni).
Pensando alle 2 tabelle come insiemi, il risultato ottenuto sfruttando questa tipologia di join coincide con tutte le coppie di record (A,B) (dove A e B appartengono rispettivamente a TabellaA e TabellaB) per cui vale la relazione:

A.ChiaveA=B.ChiaveA

In termini insiemistici è il prodotto cartesiano di tutti gli A e B appartenenti all'intersezione delle 2 tabelle TabellaA ∩ TabellaB.

Questa tipologia di query viene usata quando voglio estrarre da una tabella master quei record per i quali si è manifestata una o più volte una certa caratteristica, documentata nei record registrati nell'apposita tabella slave.

Esempio: Si consideri la struttura dati seguente (il file può essere scaricato cliccando qui):

contenente nella tabella studenti questi dati

e nella tabella interrogazioni questi altri

La seguente query

SELECT Studenti.Nominativo
FROM Studenti, Interrogazioni
WHERE Studenti.IdStudente=Interrogazioni.IdStudente 

oppure la sua scrittura alternativa

SELECT Studenti.Nominativo
FROM Studenti INNER JOIN Interrogazioni
ON Studenti.IdStudente=Interrogazioni.IdStudente 

nella GUI di creazione delle query di Access si presenta in questo modo:

e fornisce questo elenco:

Come si può notare alcuni nominativi risultano duplicati ed esattamente quelli che hanno effettuato più interrogazioni. Per eliminare i duplicati basta raggruppare rispetto al campo Nominativo (suppongo che non vi siano casi di omonimia)

SELECT Studenti.Nominativo
FROM Studenti, Interrogazioni
WHERE (Studenti.idStudente=Interrogazioni.IdStudente)
GROUP BY Studenti.Nominativo;

oppure posso usare la clausola distinct

SELECT DISTINCT Studenti.Nominativo
FROM Studenti, Interrogazioni
WHERE (Studenti.idStudente=Interrogazioni.IdStudente);

LEFT JOIN / RIGHT JOIN - OUTER JOIN

Si considerino ancora due tabelle TabellaA e TabellaB collegate mediante il campo ChiaveA.

Il modello sintattico seguente:

SELECT TabellaA.*, TabellaB.* FROM TabellaA LEFT JOIN TabellaB
ON TabellaA.ChiaveA=TabellaB.ChiaveA

o in alternativa

SELECT TabellaA.*, TabellaB.* FROM TabellaB RIGHT JOIN TabellaA
ON TabellaA.ChiaveA=TabellaB.ChiaveA 

è quello relativo alla LEFT JOIN / RIGHT JOIN (OUTER JOIN). Lo schema grafico associato a questa tipologia di join è il seguente:

Si osservi che il collegamento è rappresentato da una semplice freccia uscente dalla tabella il cui nome si trova alla sinistra della clausola LEFT JOIN (o a destra della clausola RIGHT JOIN).

La LEFT JOIN è utilizzata quando il nostro obiettivo è quello di estrarre  tutti i record di TabellaA mentre di TabellaB solo quelli collegati (tramite il campo ChiaveA).

Quindi il risultato di una query che utilizza la LEFT JOIN prende in considerazione tutti i record A di TabellaA e solo i record B di TabellaB che risultano collegati (tramite il campo ChiaveA) ad un record A di TabellaA.
Per quanto riguarda i record A di TabellaA distinguiamo:
- nel caso gli A abbiano uno o più record B collegati questi verranno visualizzati (durante l'esecuzione della query) ripetutamente un numero di volte pari al numero di record  B associati in TabellaB.
- nel caso gli A non abbiano alcun record B di TabellaB corrispondente questi saranno comunque visualizzati ma al posto dei campi del record B collegato (che è inesistente!) verranno messi dei valori NULL. Ricercando questi valori NULL è possibile stabilire quali record A di TabellaA non hanno un corrispondente B in TabellaB.

Pensando le due tabelle come insiemi abbiamo che il risultato ottenuto con questa tipologia di join coincide con le coppie  (A,β) dove A appartiene a TabellaA mentre β è un record B di  TabellaB collegato ad A (se esiste!) per cui vale la relazione A.ChiaveA=B.ChiaveA . Qualora non esistano record B collegati β assume il valore NULL.

Tali coppie prendono quindi origine dalla zona indicata in rosso della figura a sinistra

I record A di TabellaA che non stanno nell'intersezione con TabellaB verranno quindi abbinati sempre con valori NULL.

Questa tipologia di query viene usata quando voglio estrarre da una tabella master quei record per i quali non si è mai manifestata  una certa caratteristica, documentata nei record registrati nell'apposita tabella slave.

Utilizzando il seguente modello sintattico:

SELECT TabellaA.* FROM TabellaA LEFT JOIN TabellaB
ON TabellaA.ChiaveA=TabellaB.ChiaveA
WHERE TabellaB.QualsiasiCampo IS NULL

oppure

SELECT TabellaA.* FROM TabellaB RIGHT JOIN TabellaA
ON TabellaA.ChiaveA=TabellaB.ChiaveA
WHERE ISNULL(TabellaB.QualsiasiCampo)

possiamo estrarre i record A di TabellaA che appartengono all'insieme differenza TabellaA/TabellaB ovvero quei record  A che non hanno un corrispondente B in TabellaB.

Questa tipologia di join viene utilizzata anche per identificare le differenze, in termini di record presenti, tra due tabelle strutturalmente identiche. Si pensi ad una banca che deve allineare il database centrale dei clienti con quelli contenuti nei db decentrati gestiti dalle sue filiali.

Esempio: Si consideri ancora la struttura relativa alle interrogazioni precedentemente vista. La seguente query

SELECT Studenti.*, Interrogazioni.*
FROM Studenti LEFT JOIN Interrogazioni
ON Studenti.IdStudente=Interrogazioni.IdStudente

oppure la sua equivalente

SELECT Studenti.*, Interrogazioni.*
FROM Interrogazioni RIGHT JOIN Studenti
ON Studenti.IdStudente=Interrogazioni.IdStudente

nella GUI di creazione delle query di Access si presentano in questo modo:

e forniscono quest'elenco

Le linee rosse evidenziano i campi della tabella Interrogazioni lasciati a Null. Tali righe corrispondono agli studenti che non hanno ancora affrontato una verifica. Valutando la presenza del valore Null su una delle colonne associate alla tabella "Interrogazioni" posso estrarre gli studenti non ancora interrogati. La query successiva realizza proprio questo scopo:

SELECT Studenti.Nominativo AS [Da Interrogare]
FROM Studenti LEFT JOIN Interrogazioni
ON Interrogazioni.IdStudente = Studenti.idStudente
WHERE (Interrogazioni.idInterrogazione Is Null);

o in alternativa (usando la funzione IsNull):

SELECT Studenti.Nominativo AS [Da Interrogare]
FROM Studenti LEFT JOIN Interrogazioni
ON Interrogazioni.IdStudente = Studenti.idStudente
WHERE IsNull(Interrogazioni.idInterrogazione);

NO JOIN

Si considerino sempre due tabelle TabellaA e TabellaB ma si ignorino eventuali collegamenti tra i loro campi.

Il modello sintattico seguente:

SELECT TabellaA.*, TabellaB.*
FROM TabellaA, TabellaB

è quello relativo alla NO JOIN. Lo schema grafico associato a questa tipologia di join è il seguente:

Si osservi che tra le 2 tabelle non viene evidenziato alcun collegamento.

La NO JOIN è utilizzata quando il nostro obiettivo è quello di analizzare tutte le possibili combinazioni di record (A,B) ottenute accoppiando ciascun record A di TabellaA con tutti i record B di TabellaB.

Il risultato dell'esecuzione di una query di questo tipo è un elenco dove ogni riga A di TabellaA viene ripetuta per ogni record B di TabellaB.  

Pensando alle due tabelle come insiemi il risultato corrisponde al loro prodotto cartesiano ovvero a tutte le coppie di record(A,B)dove A appartiene a TabellaA e B a TabellaB.. Tali coppie prendono quindi origine dalla zona indicata in rosso che corrisponde all'insieme unione

Esempio: Si consideri ancora la struttura relativa alle interrogazioni precedentemente vista. La seguente query

SELECT Studenti.*, Interrogazioni.*
FROM Interrogazioni, Studenti

si presenta nel generatore delle query di Access in questo modo:

e produce questo elenco:

Modificandola opportunamente posso ottenere tutte le possibili combinazioni studente/materia con l'eventuale media dei voti (se è stato interrogato almeno una volta!) a lato. 

SELECT Studenti.Nominativo, Interrogazioni.Materia,
IIf(
     Sum(IIf(Interrogazioni.IdStudente=Studenti.IdStudente,1,0))=0,
     "Senza voto",
     Sum(IIf(Interrogazioni.IdStudente=Studenti.IdStudente,Voto,0))      /Sum(IIf(Interrogazioni.IdStudente=Studenti.IdStudente,1,0))
) AS VotoOttenuto
FROM Interrogazioni, Studenti
GROUP BY Studenti.Nominativo, Interrogazioni.Materia
ORDER BY Studenti.Nominativo, Interrogazioni.Materia;

La sua esecuzione produce:

Mediante la query incrociata (che vedremo nella parte 6°)

TRANSFORM First(Registro.VotoOttenuto) AS VotoAssegnato
SELECT Registro.Nominativo FROM Registro
GROUP BY Registro.Nominativo
PIVOT Registro.Materia;

ottengo un report più leggibile che presenta la situazione di tutta la classe per ogni materia: