Hi, I've 2 tables of payments.... 1: payment emission (P: --> ID,ID_cli,tot) 2: part payments (A: --> ID,P_ID,val)
I'd like to retrieve all payment emissions, with all relative part payments....(with a left join), for all ID_cli
and I'd like to calculate the rest to pay...like tot-SUM(val).
but I don't know how to obtain the subtotal of all the part payments reative to each payment,
so to calculate the remaining payment
this is my real query:
SELECT T.ID ID_tes, importo, tipo_doc, tot_doc, tot_doc-SUM(importo) saldo,SUM(importo) FROM mag_movtes T LEFT JOIN mov_cas C ON T.ID=C.ID_tes AND verso='E' AND C.deleted=0 WHERE T.rif_CF='C' AND T.deleted=0 AND T.ID_CF=426 GROUP BY T.ID,C.ID HAVING tot_doc-SUM(importo)!=0
these are the tables:
TABLE `mag_movtes` `ID` int(11) unsigned NOT NULL auto_increment, `data_mov` date default NULL, `rif_CF` set('C','F') default NULL, `ID_CF` int(11) unsigned default NULL, `ID_cau` tinyint(3) unsigned default NULL, `data_doc` date default NULL, `num_doc` tinytext, `tipo_doc` set('B','F','C','I','E') default NULL, `imp_doc` decimal(6,2) default NULL, `sconto` decimal(4,2) default NULL, `tot_doc` decimal(6,2) default NULL, `deleted` tinyint(1) unsigned NOT NULL default '0',
TABLE `mov_cas`
`ID` int(10) unsigned NOT NULL auto_increment,
`data` date NOT NULL default '0000-00-00',
`rif_CF` enum('C','F') NOT NULL default 'C',
`ID_CF` int(10) unsigned NOT NULL default '0',
`ID_tes` int(10) unsigned NOT NULL default '0',
`descr` tinytext,
`importo` decimal(6,2) default NULL,
`verso` enum('E','U') NOT NULL default 'E',
`deleted` tinyint(1) NOT NULL default '0',