Query per trovare somma valori con riferimenti esterni

[ilc0rv0]

Ciao a tutti, scusate per il titolo un pò contorto ma non sapevo cosa scrivere

passo subito al problema:

ho un database con queste tabelle:

mysql> describe particolari;
+-------------+----------------------+------+-----+---------+----------------+
| Field       | Type                 | Null | Key | Default | Extra          |
+-------------+----------------------+------+-----+---------+----------------+
| id_p        | smallint(5) unsigned |      | PRI | NULL    | auto_increment |
| codice      | varchar(45)          |      | UNI |         |                |
| livello     | tinyint(3) unsigned  |      |     | 0       |                |
| prezzo      | float(4,2)           |      |     | 0.00    |                |
| descrizione | varchar(150)         |      |     |         |                |
| abbrev      | varchar(45)          |      |     |         |                |
+-------------+----------------------+------+-----+---------+----------------+

mysql> describe consegne;
+-------------+----------------------+------+-----+------------+----------------+
| Field       | Type                 | Null | Key | Default    | Extra          |
+-------------+----------------------+------+-----+------------+----------------+
| id_c        | int(10) unsigned     |      | PRI | NULL       | auto_increment |
| bolla       | varchar(15)          |      |     |            |                |
| data        | date                 |      |     | 0000-00-00 |                |
| particolare | smallint(5) unsigned |      | MUL | 0          |                |
| um          | varchar(5)           |      |     |            |                |
| quantita    | smallint(5) unsigned |      |     | 0          |                |
+-------------+----------------------+------+-----+------------+----------------+

mysql> describe forniture;
+-------------+----------------------+------+-----+------------+----------------+
| Field       | Type                 | Null | Key | Default    | Extra          |
+-------------+----------------------+------+-----+------------+----------------+
| id_f        | int(10) unsigned     |      | PRI | NULL       | auto_increment |
| ddt         | varchar(15)          |      |     | 0000-00-00 |                |
| data        | date                 |      |     | 0000-00-00 |                |
| particolare | smallint(5) unsigned |      | MUL | 0          |                |
| um          | varchar(5)           |      |     |            |                |
| quantita    | smallint(5) unsigned |      |     | 0          |                |
| completata  | enum('SI','NO')      |      |     | NO         |                |
+-------------+----------------------+------+-----+------------+----------------+

mysql> describe cons_forn;
+-----------+----------------------+------+-----+---------+----------------+
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id_cf     | int(10) unsigned     |      | PRI | NULL    | auto_increment |
| consegna  | int(10) unsigned     |      | MUL | 0       |                |
| fornitura | int(10) unsigned     |      | MUL | 0       |                |
| quantita  | smallint(5) unsigned |      |     | 0       |                |
+-----------+----------------------+------+-----+---------+----------------+

mysql> describe saldature;
+--------+----------------------+------+-----+---------+----------------+
| Field  | Type                 | Null | Key | Default | Extra          |
+--------+----------------------+------+-----+---------+----------------+
| id_s   | int(10) unsigned     |      | PRI | NULL    | auto_increment |
| figlio | smallint(5) unsigned |      | MUL | 0       |                |
| madre  | smallint(5) unsigned |      | MUL | 0       |                |
+--------+----------------------+------+-----+---------+----------------+ 

che spiego brevemente:

nella mia ditta (metalmeccanica) saldiamo dei particolari per un cliente.
In pratica loro ci forniscono 1 o più particolari che noi andiamo a saldare e poi li restituiamo.

Quando ci forniscono un solo particolare noi produciamo il 2.

nella tabella PARTICOLARI ci sono tutti i particolari, suddivisi in livelli (1 e 2) dove livello 2 sono quelli che loro danno a noi e livello 1 sono quelli che noi restituiamo a loro (quindi un livello 1 e una composizione di uno o più livelli 2).

nella tabella CONSEGNE ci sono tutti i dati delle nostre consegne (numero bolla,quantita,data ecc ecc dei particolari di livello 1)

nella tabella FORNITURE ci sono tutti i dati delle loro consegne (numero ddt,quantita,data ecc ecc dei particolari di livello 2)

nella tabella CONS_FORN ci sono tutti i dati delle nostre consegne relativamente alle loro forniture (con una nostra consegna potremmo utilizzare una parte, una o più di una delle loro forniture)

ad esempio:
loro ci danno
3000 pezzi di un particolare in data 01/01/08
2000 pezzi dello stesso particolare in data 02/02/08

noi consegnamo in data 03/01/08 4000 pezzi quindi saldiamo la fornitura da 3000 e usiamo 1000 di quella da 2000

nella tabella SALDATURE ci sono le relazioni tra i particolari di livello 1 (madri) e 2 (figli)

ad esempio
particolare 1 -- livello 2
particolare 2 -- livello 2
particolare 3 -- livello 1 = particolare 1 + particolare 2


ora il mio problema sorge quando devo fare un riepilogo di una consegna effettuata!
Finora sono giunto a questa soluzione che pare funzionare se non per un particolare:

select ddt, data, p.codice, f.quantita as forniti, sum(cf.quantita) as consegnati 
from cons_forn cf,forniture f,particolari p 
where consegna in (
select id_c from consegne 
where particolare=(select particolare from consegne where id_c='$id_c') 
and data<=(select data from consegne where id_c='$id_c')
) 
and fornitura=id_f 
and f.particolare=p.id_p 
and f.completata='NO' 
group by fornitura 
order by particolare,data 

il problema è questo:
quando faccio un riepilogo consegna di un particolare di livello 1 formato da un solo particolare di livello 2 è tutto ok, quando invece utilizzo un particolare di livello 1 formato da più di un particolare di livello 2, nella somma delle quantita consegnate totali mi compare solo quello del particolare in esame e non quello di tutti i particolari utilizzati!

lo so che è contorto da morire ma qualcuno potrebbe darmi una mano?

grazie infinite!

[ilc0rv0]

cerco di chiarire un pò meglio con un esempio

i particolare COD.112 e COD.212 utilizzano un particolare di livello 2 in comune (il particolare con codice D5150113)

quindi quando consegno il COD.112 o il COD.212 consegno sempre anche il D5150113.

ora se faccio un riepilogo di una consegna di 1900 pezzi COD.112, dopo aver consegnato anche 1900 pezzi di COD.212 ottengo questo risultato

d.d.t. Data Particolare Quantità fornita Quantità consegnata Quantità da consegnare
107/002 04/07/2008 D5160112C 850 850 0
133/002 28/08/2008 D5160112C 4000 1050 2950
107/002 04/07/2008 D5150113 4103 1900 2203

mentre avrei dovuto ottenere

d.d.t. Data Particolare Quantità fornita Quantità consegnata Quantità da consegnare
107/002 04/07/2008 D5160112C 850 850 0
133/002 28/08/2008 D5160112C 4000 1050 2950
107/002 04/07/2008 D5150113 4103 3800 303

è più chiaro ora?

[ilc0rv0]

nessuno sa darmi una mano?

[ilc0rv0]

ho risolto in questo modo!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!

select p.codice,f.ddt,f.data,f.quantita as forniti,sum(cf.quantita) as consegnati,f.quantita-sum(cf.quantita) as daconsegnare
                from cons_forn cf,forniture f,consegne c,particolari p
                where fornitura in (
                    select id_f 
                    from forniture 
                    where particolare in (
                        select figlio 
                        from saldature 
                        where madre=(
                            select particolare 
                            from consegne 
                            where id_c='$id_c')
                        )
                ) 
                and id_f=fornitura 
                and id_c=consegna 
                and id_p=f.particolare 
                and cf.id_cf<=(select id_cf from cons_forn where consegna='$id_c' order by id_cf desc limit 0,1) 
                group by f.particolare,f.id_f 
                order by f.particolare,cf.id_cf