Aiuto query complessa [mysql]!

[gta3!]

Ciao ragazzi.
Sto realizzando una query che mi serve per recuperare diversi dati. Sembra funzionare tutto, mi manca un'ultima cosa che però proprio non riesco a fare.

Ho intenzione di matenere una query unica, perchè non riuscirei poi a gestire bene tutto il resto del contenuto della mia pagina.

Ho una tabella più o meno così:


[sub_lotti]
ID_PRODOTTO_QUANTITA | ID_LOTTO | QUANTITA |
14 | 20 | 2 |
24 | 20 | 4 |
13 | 20 | 3 |
14 | 22 | 6 |


e poi ho un'altra tabella più o meno così:

[prodotti]
ID_PRODOTTO_QUANTITA | PREZZO |
14 | 4 |
24 | 9 |
13 | 2 |


Quello che mi servirebbe è di andare in sub_lotti, recuperare la quantità e l'id prodotto quantita, spostarmi in prodotti, cercare quel prodotto quantità e moltiplicarlo per il suo prezzo, in modo di avere il prezzo totale:

Ad esempio:

ID_PRODOTTO_QUANTITA = 14
2 (sub_lotti.Quantita) * 4 (prodotti.prezzo)


Io una cosa del genere riesco a farla con la mia query, ma nel casoio voglio trovare tutti i prezzi dei prodotti che hanno come sub_lotti.ID_LOTTO 20, non riesco dopo averli moltiplicato a sommarli...scusate il gioco di parole

Questa è la query che uso io:

SELECT lotti.ID_lotto, lotti.nome, lotti.testo, lotti.caratt, lotti.stato, prodotti.thumb, prodotti.prezzo3, (sub_lotti.Quantita * prodotti.prezzo3) as totale
FROM prodotti, sub_prodotti
INNER JOIN (
lotti
INNER JOIN sub_lotti ON lotti.ID_lotto = sub_lotti.ID_lotto
) ON sub_prodotti.ID_Prodotto_Quantita = sub_lotti.ID_Prodotto_Quantita
WHERE prodotti.asta =0
AND prodotti.stato =1
AND sub_prodotti.ID_Prodotto = prodotti.id
AND lotti.stato =1
GROUP BY lotti.ID_lotto
ORDER BY prodotti.id DESC 

Avete qualche soluzione?

[platone]

Hai due alternative.

1) utilizzare le viste Mysql VIEWS, in questo caso viene creata una tabella temporanea permanente (lo so che sembra un controsenso ma è così), dove aggiornando le due tabelle sottostanti verrà aggiornata anche tale tabella.

In questo caso puoi fare delle query WHERE direttamente sulla tabella risultato.

2) Oppure semplicemente ripetere la moltiplicazione anche nella clausola WHERE

SELECT
lotti.ID_lotto,
lotti.nome,
lotti.testo,
lotti.caratt,
lotti.stato,

prodotti.thumb,
prodotti.prezzo3,
(sub_lotti.Quantita * prodotti.prezzo3) as totale

FROM prodotti, sub_prodotti
INNER JOIN (
lotti
INNER JOIN sub_lotti ON lotti.ID_lotto = sub_lotti.ID_lotto
) ON sub_prodotti.ID_Prodotto_Quantita = sub_lotti.ID_Prodotto_Quantita

WHERE prodotti.asta =0
AND prodotti.stato =1
AND sub_prodotti.ID_Prodotto = prodotti.id
AND lotti.stato =1
AND (sub_lotti.Quantita * prodotti.prezzo3) = \"".$variabile."\" 
ORDER BY prodotti.id DESC

3) puoi anche usare la clausola HAVING
che è come una clausola WHERE ma viene eseguita solo alla fine della query e quindi non è molto rapida lavorando solo al termine del processo.

PS: ho visto che usi i GRUOP BY ma poi non usi nemmeno una funzione di raggruppamento, in questo caso non serve affatto gruppare anzi rischi di sovrascrivere alcune info.

[gta3!]

Ho provato a usare la tabella temporanea sul database, ma ancora non riesco a sommare i valori

Questa è la query che ho usato:

CREATE OR REPLACE VIEW 
lotti_temp (ID_lotto,nome,testo,caratt,stato,thumb,prezzo3,totale)

AS 

SELECT lotti.ID_lotto, lotti.nome, lotti.testo, lotti.caratt, lotti.stato, prodotti.thumb, prodotti.prezzo3,(sub_lotti.Quantita * prodotti.prezzo3) as totale

FROM prodotti, sub_prodotti
INNER JOIN (
lotti
INNER JOIN sub_lotti ON lotti.ID_lotto = sub_lotti.ID_lotto
) ON sub_prodotti.ID_Prodotto_Quantita = sub_lotti.ID_Prodotto_Quantita

WHERE prodotti.asta =0
AND prodotti.stato =1
AND sub_prodotti.ID_Prodotto = prodotti.id
AND lotti.stato =1
GROUP BY lotti.ID_lotto
ORDER BY prodotti.id DESC 

Quel totale però, deve avere ancora la moltiplicazione di un'altro prodotto che sta sempre in quel lotto...eppure anche in questo modo non riesco...



Help

[platone]

Scusa ma non riesco ad astrarre se non ho chiari i campi delle tabelle.
Scrivi un esempio di tutte le tabelle coinvolte.

Poi scrivi un esempio di come vorresti la tua tabella finale.


PS: mi dispiace di averti consigliato le VIEWS, ho fatto ricerche e sembra proprio che MySql non riesca ad essere performante quando esegue le VIEWS, ho uno script che sta facendo un benchmark sulla stessa query (con e esenza views) e di fisso le views consumano 0.2 secondi in più in SELECT.

Posta la struttura delle tabelle e ti scrivo il codice per creare correttamente i JOIN.