[mySQL] - problema estrazione dati

[Manzo]

Ciao a tutti.
Ho un problema con una query su mySQL e non riesco a trovare info su come risolvere.
Ho 3 tabelle.
TABELLA 1: ANAGRAFE
- IdAnagrafe
- Nome
- Cognome

TABELLA 2: FRANCOBOLLI
- IdAcquistoFrancobolli
- ImportoFrancobolli
- AnagrafeId

TABELLA 3: SPESE
- IdSpesa
- ImportoSpesa
- AnagrafeId

Non aggiungo nulla sulla tabella anagrafe, le altre 2 hanno la chiave esterna su AnagrafeId.

Ora, quello che vorrei fare è estrarre tutte le persone in anagrafe ed ottenere quanti soldi in francobolli ciascuno ha a disposizione.
Riesco a fare 2 query separate (tabella1+tabella2) e (tabella1+tabella3) con tutti i nominativi e il totale relativo.
Non riesco assolutamente a mettere insieme le 3 tabelle...

tabella1+tabella2
SELECT anagrafe.nome, anagrafe.cognome, Sum(AcquistoFrancobolli.ImportoFrancobolli) AS TotaleFrancobolli
FROM anagrafe LEFT JOIN AcquistoFrancobolli ON anagrafe.IdAnagrafe=AcquistoFrancobolli.TitolareId
GROUP BY anagrafe.nome, anagrafe.cognome

tabella1+tabella3
SELECT anagrafe.nome, anagrafe.cognome, Sum(SpesePostali.ImportoSpedizione) AS TotaleSpedizioni
FROM anagrafe LEFT JOIN SpesePostali ON anagrafe.IdAnagrafe=SpesePostali.MittenteSpedizion eId
GROUP BY anagrafe.nome, anagrafe.cognome

Pensavo di risolvere così: tabella1+2+3

SELECT anagrafe.nome, anagrafe.cognome, Sum(AcquistoFrancobolli.ImportoFrancobolli) AS TotaleFrancobolli
FROM (anagrafe LEFT JOIN AcquistoFrancobolli ON anagrafe.IdAnagrafe=AcquistoFrancobolli.TitolareId )
LEFT JOIN SpesePostali ON anagrafe.IdAnagrafe=SpesePostali.MittenteSpedizion eId
GROUP BY anagrafe.nome, anagrafe.cognome


Se metto insieme le due con una join mi sbagli i totali (se ci sono spese/acquisti multipli per un utente)

Qualcuno mi riesce ad illuminare?
Ciao!
Cesare

[piero.mac]

probabile errore di struttura del database.

TABELLA 1: ANAGRAFE
- IdAnagrafe
- Nome
- Cognome

TABELLA 2: FRANCOBOLLI
- IdFrancobollo
- descrizione
- ImportoFrancobollo

TABELLA 3: SPESE (Acquisti)
- IdSpesa
- ImportoSpesa
- IdAnagrafe
- IdFrancobollo
- IdAcquisto


LA tabella SPESE dovrebbe avere un record per ogni francobollo acquistato. Cosi' di problemi non ne dovresti avere.

[Manzo]

no, non direi.
non mi serve sapere ogni singolo francobollo come è stato speso.
Mi basta sapere quanti soldi in francobolli sono stati investiti (rectius quanti francobolli sono stati comprati -> tabella francobolli)
e quante spedizioni sono state effettuate (rectius quanti francobolli sono stati spesi per le spedizioni).
Il mio obiettivo nella query è di sapere quanti soldi sono ancora disponibili per le spedizioni. Se un utente non ha abbastanza soldi in francobolli farà un nuovo acquisto (che verrà inserito nella tabella francobolli).

Il problema è proprio di logica nell'estrazione.

Il fatto è che facendo la query come ho indicato i valori vengono sballati.
esempio:
utente 1
Acquistati francobolli in 2 tornate diverse, una volta 10 euro, la seconda 20 euro
Ha effettuato 3 spedizioni (una da 4 euro, una da 5 e una da 5)
il risultato della query dovrebbe essere:
Utente 1 totale_francobolli=30 euro totale_spese=14 euro (quindi a disposizione 16 euro)

In realtà sarà:
utente 1 totale_francobolli=90 euro totale_spese=28 euro (a disposizione 62 euro!

Per qualche ragione moltiplica la SOMMA degli acquisti per il NUMERO delle spese (30 * 3 spese) e la SOMMA delle spese per il NUMERO degli acquisti (14 * 2 acquisti)

e non capisco perchè!!

in realtà una soluzione al problema già c'è.

Faccio una query (tab1+tab2) e man mano che faccio scorrere il recordset faccio una seconda query per estrarre i dati della tab 3.
Ma in questo modo i tempi si allungano (devo aprire un secondo recordset per ogni record della prima estrazione...)

mah!

cesare

[piero.mac]

Tra i due post ho capito ben poco. Parti dal principio che solo tu sai di quello che parli.

in tab1 hai i dati utente, in tab2 il deposito per acquisto in francobolli, nella tab3 le spese ed il numero di spedizioni.

Quindi il totale deposito meno la somma spesa dara' il resto.

SELECT *,
SUM(tab2.deposito) - SUM(tab3.spese) as resto
from tab1
left join tab2 on tab1.id = tab2.id_tab1
left join tab3 on tab1.id = tab3.id_tab1
where tab1.nome = 'pippo'
and tab1.cognome = 'pallino'
GROUP by nome, cognome

[Manzo]

Hai capito perfettamente.
Infatti la query che mi proponi è esattamente quello di cui ho bisogno.
L'unica differenza è che non filtro per nome e cognome perchè ho bisogno di tutto l'elenco di persone con il corrispettivo avanzo.

Quindi la tua query viene corretta così:

SELECT *, SUM(tab2.deposito) - SUM(tab3.spese) as resto
from tab1
left join tab2 on tab1.id = tab2.id_tab1
left join tab3 on tab1.id = tab3.id_tab1
GROUP by nome, cognome

Poi in realtà io ho aggiunto delle parentesi (e forse questo potrebbe essere l'errore... ma diversamente non funziona)
cioè:
SELECT *, SUM(tab2.deposito) - SUM(tab3.spese) as resto
from
(
tab1
left join tab2 on tab1.id = tab2.id_tab1
)
left join tab3 on tab1.id = tab3.id_tab1
GROUP by nome, cognome

ed ecco il problema. I conti non tornano.
se in tab2 e tab3 ci sono più record relativi ad un utente presente in anagrafe (cioè se l'utente ha effettuato più spedizioni o più acquisti di francobolli) i totali non corrispondono più!

cesare

[piero.mac]

Io ti ho suggerito un query standard per la tua esigenza.

Poi se devi metterci dati diversi nel where o da altre parti devi vedertela tu. Quello che dici va bene oppure male... chi lo sa. Bisognerebbe conoscere bene struttura e contenuto del database. Di certo e' che se rispetti l'equi-join delle colonne id = id, che le operazioni siano 1 o 10 e' lo stesso, perche' chi viene valutato sara' l'equi-join e non il numero delle ricorrenze che saranno semplicemente addizionate tra di loro.

Le parentesi servono a nulla tra from e join... tanto sono tutte in AND...

[Manzo]

Bisognerebbe conoscere bene struttura e contenuto del database.

Questa è la struttura.
ANAGRAFE
- IdAnagrafe
- Nome
- Cognome

FRANCOBOLLI
- IdAcquistoFrancobolli
- ImportoFrancobolli
- AnagrafeId

SPESE
- IdSpesa
- ImportoSpesa
- AnagrafeId

I contenuti di FRANCOBOLLI rappresentano la cifra spesa da ciascun utente presente in anagrafe per l'acquisto di francobolli. Ovviamente un utente può fare più acquisti di francobolli, quindi per ogni acquisto ci sarà un IdAcquistoFrancobolli nuovo e l'importo corrispondente.
Il contenuto di SPESE è ciascun invio di lettere. Quindi ogni utente presente in anagrafe che effettua una spedizione avrà qui il suo record. Tizio ha speso 2,50 euro, in un secondo record risulterà che tizio ha speso 1,35 euro per un secondo invio etc etc.

La query standard anche a me pareva poter funzionare, ma ti ripeto che nel momento in cui la faccio girare e ho più acquisti e/o più spese di spedizione per lo stesso utente di anagrafe i conti non tornano più!

Scusa se mi permetto di insistere. Sembra un problema banale... ma ci non credo che sia così...

cesare

[piero.mac]

Anagrafe
- id                     25
- nome                   pippo
- cognome                cecco

FRANCOBOLLI 
- IdAcquistoFrancobolli  2
- ImportoFrancobolli     25.00
- AnagrafeId             25

- IdAcquistoFrancobolli  3
- ImportoFrancobolli     15.10
- AnagrafeId             25

SPESE 
- IdSpesa                4
- ImportoSpesa           6.30
- AnagrafeId             25

- IdSpesa                5
- ImportoSpesa           7.30
- AnagrafeId             25

in un caso simile dove sarebbe il problema ???? non capisco che vuoi dire.

[Manzo]

Il problema è che usando la query di prima il risultato è questo:
pippo cecco tot_francobolli=80.20 tot_speso=27.20
invece di:
pippo cecco tot_francobolli=40.10 tot_speso=13.60

giuro!

[piero.mac]

Originariamente inviato da Manzo
Il problema è che usando la query di prima il risultato è questo:
pippo cecco tot_francobolli=80.20 tot_speso=27.20
invece di:
pippo cecco tot_francobolli=40.10 tot_speso=13.60

giuro!

Posta la query che usi ... dico la tua, non il mio esempio.