[mysql] Dubbio su query con Join

[fallen]

Chiedo scusa per postare un nuovo topic con lo stesso contenuto del precedente ma non posso cambiare più il titolo che per mio errore è molto furviante e anche contro le regole del forum.

Avrei bisogno di un aiuto sulla creazione di una query, che con le mie basilari conoscenze di mysql, ho paura di rendere orribile anche solo a vedersi !

Ho due tabelle

CREATE TABLE giocatori (
    id smallint(3) UNSIGNED NOT NULL auto_increment,
    nome varchar(255) DEFAULT '' NOT NULL,
    cognome varchar(255) DEFAULT '' NOT NULL,
    PRIMARY KEY (id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE roster(
    id smallint(3) UNSIGNED NOT NULL auto_increment,
    squadra smallint(3) NOT NULL,
    giocatore_1 smallint(3) NOT NULL,
giocatore_2 smallint(3) NOT NULL,
// e così via via ad arrivare a 11
    PRIMARY KEY (id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`; 

nella tabella roster i campi giocatore_1,giocatore_2 etc. contengono gli id dei giocatori della prima tabella.

In che modo fare una SELECT per recuperare la formazione di una squadra comprendendo anche i nomi dei giocatori ?
Io proverei a fare così ma non penso sia la soluzione ottimale.

SELECT r . * , g1 . * , g2 . *
FROM roster r
INNER JOIN giocatori g1 ON g1.id = r.giocatore_1
INNER JOIN giocatori g2 ON g2.id = r.giocatore_2
// etc fino a 11
WHERE r.squadra = 1 

O è proprio così che si deve fare ?

Grazie a tutti anticipatamente !

[r1cky`]

Scusa ma non ti basta mettere la chiave di roster nella tabella giocatore? Trattandosi di una relazione 1:n (o così ho capito io) è l'operazione più corretta.

[fallen]

Era il metodo che adottavo prima, salvavo la squadra in cui milita il giocatore nella tabella giocatore ma vorrei salvare le varie formazioni nel corso del tempo ( ogni 6 mesi cambiano ) e avevo pensato di dividere le informazioni in due tabelle.

[digitos]

La relazione che ha messo in piedi è strutturata in modo anomalo.
Ti consiglio di procedere per un'altra strada, considerando anche che l'appartenenza del giocatore a una squadra può avere una durata temporanea.
Allora io ad esempio relazionerei il giocatore anche la squadra.
Una cosa del genere

TABELLA GIOCATORE

id
nome
cognome

TABELLA SQUADRA

id
nome squadra

TABELLA ROSTER

id
id_giocatore
id_squadra


Poi potresti aggiungere dei campi DATE o TIMESTAMP per stabilire in un determinato periodo in quale squadra giocava un giocatore.

Se ti fa comodo, potresti costruirti anche una quarta tabella dove ospitare l'elenco di relazioni fra la tabella squadra e la tabella giocatori così da avere direttamente un archivio relativo alle squadre in cui ha militato ogni giocatore. la butto lì

[fallen]

grazie ! Così in effetti la query per il recupero delle formazioni è molto più semplice ! Non so perchè ma mi ero fissato nello strutturare la tabella roster in quel modo che forse dal punto di vista teorico non è nemmeno troppo sbagliata, ma dal punto di vista pratico non era proprio il massimo.