Estrarre valori MAX da un db con una left join

maxus68

Utente Attivo
1 Nov 2016
23
0
1
52
Ciao a tutti e buone feste!
Ho bisogno di estrarre da un DB un elenco di atleti e il relativo valore MAX per ogni gara a cui partecipano, esempio:

IDGARA 1
Atleta 1 max 5
Atleta 2 max 4

IDGARA 2
Atleta 1 max 17
Atleta 2 max 18

Con la query che ho costruito riesco ad estrarre gli atleti nelle rispettive gara ma il valore MAX non si riferisce alla singola gara ma solo al singolo atleta. In sostanza se l'atleta 1 in tutte le gare cui partecipa ha fatto registrare un valore MAX di 30 questo verrà preso anche nelle altre gare.
Seguendo l'esempio fatto sopra la query mi riporta:

IDGARA 1
Atleta 1 max 17 (invece di 5)
Atleta 2 max 18 (invece di 4)

IDGARA 2
Atleta 1 max 17
Atleta 2 max 18

La query che ho scritto è la seguente:
SQL:
SELECT tabella_sport_atleti.id_nominativo, tabella_sport_atleti.id_gara, MAX(risultati.valore) AS best, tabella_atleti.nominativo, tabella_atleti.classe
FROM tabella_sport_atleti
LEFT JOIN tabella_atleti ON tabella_atleti.id_nominativo = tabella_sport_atleti.id_nominativo
LEFT JOIN risultati ON risultati.id_nominativo = tabella_sport_atleti.id_nominativo
GROUP BY tabella_sport_atleti.id_gara, tabella_sport_atleti.id_nominativo
Ora il problema l'ho capito e cioè nel raggruppare per gara e nominativo la funzione MAX ma non riesco ad applicarla.
Qualcuno mi può dare una mano per favore?

Grazie e Buon Natale a tutti

Massimo
 

Max 1

Super Moderatore
Membro dello Staff
SUPER MOD
MOD
29 Feb 2012
4.275
328
83
Grazie Massimo
grazie.gif


Un lieto Natale anche a te e tuoi cari
buon natale.gif
buon natale.gif
babbonatale.gif
babbonatale.gif
 

marino51

Utente Attivo
28 Feb 2013
2.918
164
63
Lombardia
ho letto la query senza capirci molto,
ho creato un esempio (per me più comprensibile) che spero ti sia utile per il tuo progetto,
questa la query,
SQL:
select
  p.id_atleta
, p.id_gara
, max(p.punti) as best
, a.cognome
, g.gara
from punti p
left join atleti a on p.id_atleta = a.id
left join gare g on p.id_gara = g.id
group by p.id_atleta, p.id_gara, a.cognome, g.gara
order by p.id_gara, p.id_atleta
questo il risultato
1577119568153.png
 

maxus68

Utente Attivo
1 Nov 2016
23
0
1
52
Ciao Marino,
intanto grazie perchè è quello di cui ho bisogno, tuttavia mi sono dimenticato di segnalare che l'elenco deve riportare anche coloro che non hanno avuto un punteggio.
E qui ho sbagliato io a non specificarlo nell'esempio.
In poche parole bianchi luigi deve comparire nell'elenco è ovviamente il campo deve essere nullo/0 se non ha punteggio.
Nel tuo esempio manca una tabella che io ho chiamato:
Tabella_Sport_Atleti che riporta i seguenti campi
- id_sport_atleti
- id_gara
- id_nominativo

E' da questa tabella che devo tirare fuori l'elenco, infatti qui dentro compaiono tutti coloro che partecipano ad una o più gare.

Sto facendo alcune prove seguendo la tua query ma il risultato è che mi riporta sempre il punteggio max senza distinguere la gara per partecipante.

Spero tu abbia capito il senso di quello che ho scritto :)

Grazie

Massimo
 

marino51

Utente Attivo
28 Feb 2013
2.918
164
63
Lombardia
ho modificato la query aggiungendo in fondo
SQL:
group by p.id_atleta, p.id_gara, a.cognome, g.gara
union
select
  9999999 as id_gara
, a.id
, 0 as best
, a.cognome
, 'OSSERVATORE' as gara
from @atleti a
left join @punti p
on a.id = p.id_gara
where p.id_gara is null
order by p.id_gara, p.id_atleta
con questo risultato

1577173849456.png


se serve altro, é meglio che pubblichi le tue tabelle con un esempio, perché non ho ancora capito come sono strutturate e come sono legate (indicizzate) tra loro
 

marino51

Utente Attivo
28 Feb 2013
2.918
164
63
Lombardia
ripubblico le 3 query perché le ho sistemate, con l'ultima e l'ultimo risultato, se fosse il tuo obiettivo
SQL:
-- solo risultati presenti

select
  p.id_atleta
, p.id_gara
, max(p.punti) as best
, a.cognome
, g.gara
from @punti p
left join @atleti a on p.id_atleta = a.id
left join @gare g on p.id_gara = g.id
group by p.id_atleta, p.id_gara, a.cognome, g.gara
order by id_gara, id_atleta

-- risultati presenti più non partecipanti elencati in fondo alla lista

select
  p.id_gara
, p.id_atleta
, max(p.punti) as best
, a.cognome
, g.gara
from @punti p
left join @atleti a on p.id_atleta = a.id
left join @gare g on p.id_gara = g.id
group by p.id_atleta, p.id_gara, a.cognome, g.gara
union
select
  9999999 as id_gara
, a.id as id_atleta
, 0 as best
, a.cognome
, 'OSSERVATORE' as gara
from @atleti a
left join @punti p on a.id = p.id_atleta
where p.id_gara is null
order by id_gara, id_atleta

-- risultati presenti più non partecipanti elencati per gara

select
  p.id_gara
, p.id_atleta
, max(p.punti) as best
, a.cognome
, g.gara
from @punti p
left join @atleti a on p.id_atleta = a.id
left join @gare g on p.id_gara = g.id
group by p.id_atleta, p.id_gara, a.cognome, g.gara
union
select
  x.id_gara
, x.id_atleta
, x.best
, x.cognome
, x.gara
from (
select
  g.id as id_gara
, a.id as id_atleta
, 0 as best
, a.cognome
, g.gara
from @atleti a, @gare g ) x
left join @punti p on x.id_atleta = p.id_atleta AND x.id_gara = p.id_gara
where p.id_gara is null
order by id_gara, id_atleta
1577176445649.png
 

maxus68

Utente Attivo
1 Nov 2016
23
0
1
52
Ciao Marino,
non ho ancora avuto modo di provare il tuo codice sicuramente funzionante ;)
Però volevo ringraziarti anticipatamente e farti gli auguri di Buon Natale

Comunque ti faccio sapere appena riesco...

Grazie mille

Massimo
 

maxus68

Utente Attivo
1 Nov 2016
23
0
1
52
Ciao Marino,
sono riuscito a prendere in mano il tuo codice e sto cercando di applicarlo al mio caso.
Però non capisco due cose:
- dopo il FROM e LEFT JOIN inserisci la chiocciola prima del nome della tabella, perchè? Non conoscendo il significato ho provato metterlo anche io ma mi da errore;
- nella UNION SELECT prendi i dati da una tabella x, ma non capisco quale possa essere nel mio caso.

In ogni caso ti scrivo le tabelle e le loro relazioni che sono interessate:

Risultatitabella_sport_atletitbl_gare
ID_RisultatoID_Sport_atletiID_Gara
ID_GaraID_GaraID_Categoria
ID_NominativoID_NominativoID_Sport
Punteggio

In sostanza nella tabella "Risultati" riporto tutti i punteggi per gara e nominativo, nella "tabella_sport_atleti" inserisco per ogni nominativo la gara a cui partecipa (i nominativi di questa tabella devono comparire tutti anche senza risultato nella query), mentre nella tabella "tbl_gare" inserisco la categoria e lo sport (es. Allievi maschili, Salto in alto)

Probabilmente, e correggimi se sbaglio, nella tabella risultati al posto di ID_Gara e ID_Nominativo andrebbe messo ID_Sport_atleti, altrimenti non riesco a metterla in relazione correttamente.

Se così fosse, non è un problema cambiare sono in fase di sviluppo e sto mettendo valori non reali, come la dovrei strutturare la query?

Grazie e se non ci sentiamo buon anno!!
 

marino51

Utente Attivo
28 Feb 2013
2.918
164
63
Lombardia
per provare le query, utilizzo delle tabelle temporanee, identificate dal nome preceduto dalla chiocciola,
per le tabelle effettive non va usata la chiocciola ( che credo sia una specificità del db che uso )

la x é il nome della tabella che viene generata come risultato della select from … @atleti a, @gare g ) x
é obbligatorio dare un nome alla tabella risultante, ho usato "x"

non capisco però gli indici che legano le tabelle e come possa essere definito il risultato ei una gara con la struttura che hai postato,

credo comunque che una corrispondenza possa essere,
Codice:
@punti     Risultati
id         ID_Risultato
id_gara    ID_Gara
id_atleta  ID_Nominativo
punti      Punteggio

@atleti    tabella_sport_atleti
?          ID_Sport_atleti
?          ID_Gara
id         ID_Nominativo
cognome    ?

@gare      tbl_gare
id         ID_Gara
?          ID_Categoria
?          ID_Sport
gara       ?
Buon anno anche a te !