Hello,

I got these tables and this query, but, I just can't fix it to get the data I need, any tip, advice or comment is very wellcome.

Code:
//This table has the item's initial stock at the store
CREATE TABLE `catinvin` (
  `row_id` int(11) NOT NULL auto_increment,
  `cubx` decimal(3,0) NOT NULL default '0',		// Store Id
  `itmid` varchar(9) NOT NULL default '         ',	             // Item Id
  `itminiu` decimal(9,2) NOT NULL default '0.00',	// Initial Stock
  `itminii` decimal(9,2) NOT NULL default '0.00',	            // Initial Stock Price
  `invifec` date default NULL,			// When was captured Date
  `invitim` varchar(10) NOT NULL default '  ',		// When was captured time
  `inviusr` varchar(9) NOT NULL default '   ',		// When was captured user
  PRIMARY KEY  (`row_id`),
  KEY `invitm` (`itmid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
Code:
// This table contains any item's operation at any date
CREATE TABLE `acmitms` (
  `row_id` int(11) NOT NULL auto_increment,
  `cubx` decimal(3,0) NOT NULL default '0',		// Store Id
  `aitmid` varchar(9) NOT NULL default '         ',	// Item Id
  `aitmdat` date default NULL,				// Operation Date
  `aitmcaru` decimal(9,2) NOT NULL default '0.00',	// Units IN
  `aitmcari` decimal(12,2) NOT NULL default '0.00',	// Import
  `aitmabou` decimal(9,2) NOT NULL default '0.00',	// Units OUT
  `aitmaboi` decimal(12,2) NOT NULL default '0.00',	// Import
  PRIMARY KEY  (`row_id`),
  KEY `itm_id` (`aitmid`),
  KEY `itm_dat` (`aitmdat`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
Code:
// This is Item's catalog Table
CREATE TABLE `catitms` (
  `row_id` int(11) unsigned NOT NULL auto_increment,
  `pidx` varchar(9) NOT NULL default '         ',	// Item ID
  `ppart` varchar(14) NOT NULL default '           ',	// SKU
  `pdesc` varchar(150) NOT NULL default '          ',	// Description
  `pfabr` varchar(9) NOT NULL default '         ',	// Manufacturer ID
  `pprod` varchar(9) NOT NULL default '         ',	// Product Type ID
  PRIMARY KEY  (`row_id`),
  KEY `itm_id` (`pidx`),
  KEY `itm_prod` (`pprod`),
  KEY `itm_fab` (`pfabr`)
) ENGINE=InnoDB AUTO_INCREMENT=300400 DEFAULT CHARSET=latin1
I need to get this data using 2 dates :
Code:
                  |<<<<<Date 1|>>>>>>>> Date 2 <<<<<<<<<< |
SKU | Description | SIU | SII | CARU | CARI | ABOU | ABOI | SFU | SFI

Where :
SIU  = itminiu + aitmcaru			Any date before Date1
SII  = (itminiu*itminii)+(aitmcaru*aitmcari)	""    ""   ""     ""
CARU = SUM(aitmcaru)				Any date between date1 and date2
CARI = SUM(aitmcarI)				""    ""     ""    ""     ""
ABOU = SUM(aitmabou)				""    ""     ""    ""     ""
ABOI = SUM(aitmaboi)				""    ""     ""    ""     ""
SFU = ( SIU + CARU - ABOU )
SFI = ( SII + CARI - aBOI )
So far I have done this and is working :

SELECT itmnp,itmdsc,

FORMAT( COALESCE( ( SELECT
( SELECT COALESCE( cinvi1.itminiu,0)
FROM catinvin AS cinvi1
WHERE cinvi1.itmid = xitmid AND cinvi1.cubx = 101 ) +
( SELECT COALESCE( SUM(m2.aitmcaru),0)
FROM acmitms AS m2
WHERE m2.aitmid = xitmid
AND m2.aitmdat < '2010-02-01'
AND m2.cubx = 101 ) -
( SELECT COALESCE( SUM(m3.aitmabou),0)
FROM acmitms AS m3
WHERE m3.aitmid = xitmid
AND m3.aitmdat < '2010-02-01'
AND m3.cubx = 101 ) ) , 0 ),2 ) AS sldiniuns,

FORMAT( COALESCE( ( SELECT
( SELECT COALESCE( cinvi2.itminiu * cinvi2.itminii,0)
FROM catinvin AS cinvi2
WHERE cinvi2.itmid = xitmid AND cinvi2.cubx = 101 ) +
( SELECT COALESCE( SUM(m4.aitmcari),0)
FROM acmitms AS m4
WHERE m4.aitmid = xitmid
AND m4.aitmdat < '2010-02-01'
AND m4.cubx = 101 ) -
( SELECT COALESCE( SUM(m5.aitmaboi),0)
FROM acmitms AS m5
WHERE m5.aitmid = xitmid
AND m5.aitmdat < '2010-02-01'
AND m5.cubx = 101 ) ), 0 ),2 ) AS sldiniimp,

FORMAT(tcaru,2),FORMAT(tcari,2),FORMAT(tabou,2),FORMAT(taboi,2),

FORMAT( ( SELECT COALESCE(sldiniuns+tcaru-tabou,0 ) ),2 ) AS sldfinuns,
FORMAT( ( SELECT COALESCE(sldiniimp+tcari-taboi,0 ) ),2 ) AS sldfinimp,

xitmid,AES_DECRYPT(bprd.pnm, 'mypswrd' ) AS prdnm,
AES_DECRYPT(bfab.fnm, 'mypswrd' ) AS fabnm,npalta,npaltb,itmsts,0 AS isld

FROM (
SELECT
SUM(m1.aitmcaru) AS tcaru,
SUM(m1.aitmcari) AS tcari,
SUM(m1.aitmabou) AS tabou,
SUM(m1.aitmaboi) AS taboi,
m1.aitmid AS xitmid,
AES_DECRYPT(itm1.ppart, 'mypswrd' ) AS itmnp,
AES_DECRYPT(itm1.pdesc, 'mypswrd' ) AS itmdsc,
citm.pprod AS itmprd,
citm.pfabr AS itmfab,
AES_DECRYPT(itm1.pnpalta, 'mypswrd' ) AS npalta,
AES_DECRYPT(itm1.pnpaltb, 'mypswrd' ) AS npaltb,
citm.pflgai AS itmsts
FROM acmitms AS m1
LEFT JOIN rscatsbrdg.catitms AS itm1 ON m1.aitmid = itm1.pidx

LEFT JOIN catitms AS citm ON itm1.pidx = citm.pidx

WHERE citm.pfabr = 'bDkqMSyNp'
AND m1.aitmdat BETWEEN '2010-02-01'
AND '2010-02-25'
AND m1.cubx = 101


GROUP BY xitmid
ORDER BY itmnp ) AS t
LEFT JOIN rscatsbrdg.catprds AS bprd ON itmprd = bprd.pid
LEFT JOIN rscatsbrdg.catfabs AS bfab ON itmfab = bfab.fid
ORDER BY prdnm,itmnp ;



The "problem" is, I can get only data from items that exists on acmitms and I need to get all items at the catalogo having the same manufacturer ID, it doesnt matter if the have operations or not.


Thanks a lot in advanced.