Read 119 times | Created 2014-04-17 02:44:54 | Updated 2014-04-17 02:44:54 | | |

 

DROP TABLE IF EXISTS `tbl_items`; 
CREATE TABLE IF NOT EXISTS `tbl_items` ( 
  `id` smallint(4) unsigned NOT NULL AUTO_INCREMENT, 
  `item` varchar(50) NOT NULL, 
  `stock` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM; 

INSERT INTO `tbl_items`
VALUES
(1,'ABC',1000);

DROP TABLE IF EXISTS `tbl_transactions`; 
CREATE TABLE IF NOT EXISTS `tbl_transactions` ( 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT, 
  `item_id` smallint(4) unsigned NOT NULL, 
  `out` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM; 

INSERT INTO `tbl_transactions`
VALUES
(1,1,100),
(2,1,200),
(3,1,100);

SELECT b.item,(@a:=@a-a.out)+a.out AS stok, a.out
FROM tbl_transactions a
JOIN tbl_items b ON a.item_id=b.id
JOIN (SELECT id,@a:=stock FROM tbl_items) c ON c.id=b.id;