Benvenuti alla decima lezione, in questa lezione imparerete ad applicare il Self Join.  Un Self Join non è altro che un Join applicato alla stessa tabella.

Per capire come funziona questo tipo di Join, immaginate di avere una tabella Dipendenti, in cui è presente anche l'id del responsabile di ogni dipendente. La particolarità di questa tabelle è che il responsabile di un dipendente è anche lui un dipendente, quindi è presente nella stessa tabella.

La caratteristica importante della tabella Dipendenti è che contine contemporaneamente, sia la chiave primaria, che la chiave esterna, come mostra l'immagine seguente:

Per distinguere le due tabelle con lo stesso nome dovete usare due alias di tabella differenti, come mostra l'esempio seguente:

SELECT col1,col2, ..., coln FROM tabella_a AS a JOIN tabella_a AS b ON ( ... );

Per vedere come si applica praticamente un Self Join, facciamo le seguenti considerazioni:

1) Rossi Alberto è l'Amministratore dell'azienda quindi non ha responsabili;

2) Verdi Carlo è il Direttore Generale dell'Azienda, il suo responsabile è Rossi Alberto;

3) Bianchi Silvia è il Direttore Commerciale dell'Azienda, il suo Responsabile è Verdi Carlo;

4) Neri Mara è il Responsabile delle Risorse Umane, il suo Responsabile è Verdi Carlo;

5) Rossi Luca è un Commerciale, il suo Responsabile è Bianchi Silvia;

6) Verdi Laura è un Commerciale, il suo Responsabile è Bianchi Silvia;

7) Bianchi Loredana è una Segretaria, il suo Responsabile è Neri Mara;

8) Rossi Valentina è un'Addetta all'Amministrazione, il suo Responsabile è Neri Mara;

9) Bianchi Paola  è un'Addetta all'Amministrazione, il suo Responsabile è Neri Mara;

10) Neri Marcella è un'Addetta alla Reception, il suo Responsabile è Neri Mara;

Per creare il Database Azienda, implementate lo Schema Fisico seguente:

Attraverso i comandi seguenti potete implementare il Database Azienda, in MySQL, se state usando un DBMS diverso potrebbero esserci delle differenze.

I comandi devono essere eseguiti nella sezione SQL del pannello di controllo phpMyAdmin.

STEP 1: Creazione del Database Azienda

1) Create il Database:

create database azienda;

2) Create la tabella Dipendenti:

use azienda; -- connettiti al db azienda
create table dipendenti (
    id int AUTO_INCREMENT PRIMARY KEY,
    matricola char(5) UNIQUE,
    cognome varchar(50) NOT NULL,
    nome varchar(50) NOT NULL,
    cod_fisc char(16) UNIQUE,
    luogo_di_nascita varchar(50),
    data_di_nascita date,
    id_ruolo int NOT NULL,
    id_responsabile int);

3) Create la tabella Ruoli:

use azienda; -- connettiti al db azienda
create table ruoli (
    id int AUTO_INCREMENT PRIMARY KEY,
    ruolo varchar(50) NOT NULL);

STEP 2: Inserire i ruoli nella Tabella Ruoli

use azienda; -- connettiti al db azienda

insert into ruoli (ruolo) values ('Amministratore');

insert into ruoli (ruolo) values ('Direttore Generale');

insert into ruoli (ruolo) values ('Direttore Commerciale');

insert into ruoli (ruolo) values ('Reaponsabile Risorse Umane');

insert into ruoli (ruolo) values ('Commerciale');

insert into ruoli (ruolo) values ('Segretaria');

insert into ruoli (ruolo) values ('Addetto Amministrazione');

insert into ruoli (ruolo) values ('Addetto Reception');

insert into ruoli (ruolo) values ('Addetto Delivery');

insert into ruoli (ruolo) values ('Responsabile Sicurezza');

Il risultato del lavoro fatto è il seguente:

select * from ruoli:

STEP 3: Inserire i Dipendenti nella Tabella Dipendenti

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('RA001', 'Rossi', 'Alberto','Roma','1970/12/10',1,null);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ( 'VC002','Verdi', 'Carlo','Latina','1975/04/20', 2,1);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('BS003','Bianchi', 'Silvia','Rieti', '1980/02/05', 3,2);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('NM004', 'Neri', 'Mara', 'Napoli', '1978/05/10', 4,2);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('RL005','Rossi', 'Luca', 'Salerno','1972/09/15', 5, 3);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('VL006','Verdi', 'Laura', 'Roma', '1973/11/08', 5,3);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('BL007', 'Bianchi', 'Loredana', 'Latina', '1982/03/04', 6,4);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('RV008','Rossi', 'Valentina', 'Napoli', '1986/07/13', 7, 4);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('BP009', 'Bianchi', 'Paola',  'Benevento', '1975/06/25', 7,4);

insert into dipendenti (matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile)
                    values ('NM010', 'Neri', 'Marcella', 'Salerno', '1979/05/28', 8,4);

Il risultato del lavoro fatto è il seguente:

select id, matricola, cognome, nome, luogo_di_nascita, data_di_nascita, id_ruolo, id_responsabile from dipendenti;

Creazione di una Query per visualizzare i Dipendenti, i Ruoli ed i Responsabili

Per creare la Query richiesta, serve un INNER JOIN tra la tabella Dipendenti e la tabella Ruoli, per visualizzare i ruoli e un SELF JOIN sulla tabella Dipendenti per visualizzare i Responsabili, il comando seguente vi permette di ottenere questo risultato:

select d1.id, d1.matricola, d1.cognome, d1.nome, d1.luogo_di_nascita, d1.data_di_nascita, r.ruolo, d2.cognome, d2.nome
            from dipendenti as d1 join dipendenti as d2 on (d1.id_responsabile=d2.id) join ruoli as r on (d1.id_ruolo=r.id);

L'Amministratore Rossi Alberto,corrispondente all'id 1, non viene visualizzato perché l'id_responsabile è uguale a null, quindi la condizione d1.id_responsabile=d2.id non è verificata, in un INNER JOIN oppure in un SELF JOIN vengono visualizzati solo i record che soddisfano la condizione chiave esterna uguale a chiave primaria.

Per visualizzare anche l'Amministratore dell'Azienda, dovete usare un LEFT JOIN, come mostra il comando seguente:

select d1.id, d1.matricola, d1.cognome, d1.nome, d1.luogo_di_nascita, d1.data_di_nascita, r.ruolo, d2.cognome, d2.nome
            from dipendenti as d1 left join dipendenti as d2 on (d1.id_responsabile=d2.id) left join ruoli as r on (d1.id_ruolo=r.id);

Utilizzando gli alias di colonna ed il concatenamento tra le colonne potete rendere il report ancora più professionale, come mostra il comando seguente:

select d1.id, d1.matricola, concat(d1.cognome, ' ', d1.nome) as "dipendente", d1.luogo_di_nascita, d1.data_di_nascita, r.ruolo, concat(d2.cognome, ' ',  d2.nome) as "responsabile"
            from dipendenti as d1 left join dipendenti as d2 on (d1.id_responsabile=d2.id) left  join ruoli as r on (d1.id_ruolo=r.id);

Attenzione la funzione MySQL concat permette di concatenare in un'unica colonna il congnome, uno spazio ed il nome. Con altri DBMS gli operatori di concatnamento sono || (doppio pipe) oppure il segno +, come mostra l'esempio seguente:

select d1.id, d1.matricola, d1.cognome || ' ' ||  d1.nome as "dipendente", d1.luogo_di_nascita, d1.data_di_nascita, r.ruolo, d2.cognome ||  ' ' ||  d2.nome  as "responsabile"
            from dipendenti as d1 left join dipendenti as d2 on (d1.id_responsabile=d2.id)  left join ruoli as r on (d1.id_ruolo=r.id);

Infine, per rendere il report ancora più professionale, con un ulteriore JOIN sulla tabella ruoli, potete anche aggiungere anche il ruolo del Responsabile, come mostra il comando seguente:

select d1.id, d1.matricola, concat(d1.cognome, ' ', d1.nome) as "dipendente", d1.luogo_di_nascita, d1.data_di_nascita, r1.ruolo,
            concat(d2.cognome, ' ',  d2.nome) as "responsabile", r2.ruolo as "ruolo responsabile"
            from dipendenti as d1 left join dipendenti as d2 on (d1.id_responsabile=d2.id) left join ruoli as r1 on (d1.id_ruolo=r1.id) 
           left  join ruoli as r2 on (d2.id_ruolo=r2.id) ;

Attenzione per visualizzare anche l'Amministratore è importante che anche l'ultimo JOIN sia di tipo LEFT .

<< Lezione precedente           Lezione successiva >>

T U T O R I A L S    S U G G E R I T I

• APP Mania
• Excel delle Meraviglie
• Come sviluppare un Sito con Wordpress