Estrarre valori MAX da un db con una left join

[maxus68]

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:

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]

Grazie Massimo

Un lieto Natale anche a te e tuoi cari

 

[marino51]

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,

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

 

[maxus68]

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]

ho modificato la query aggiungendo in fondo

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

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]

ripubblico le 3 query perché le ho sistemate, con l'ultima e l'ultimo risultato, se fosse il tuo obiettivo

-- 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

 

[maxus68]

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]

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:

Risultati	tabella_sport_atleti	tbl_gare
ID_Risultato	ID_Sport_atleti	ID_Gara
ID_Gara	ID_Gara	ID_Categoria
ID_Nominativo	ID_Nominativo	ID_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]

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,

@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 !