CREATE TABLE tblMainInventory(id,q); INSERT INTO "tblMainInventory" VALUES(1,40); INSERT INTO "tblMainInventory" VALUES(2,50); CREATE TABLE tblSales(id,q); INSERT INTO "tblSales" VALUES(1,30); INSERT INTO "tblSales" VALUES(2,20); CREATE TABLE tblReceives(id,q); INSERT INTO "tblReceives" VALUES(1,20); INSERT INTO "tblReceives" VALUES(2,20);
select *,t1.q-t2.q+t3.q from tblMainInventory as t1,tblSales as t2,tblReceives as t3 where t1.id=t2.id and t1.id=t3.id group by id; id|q|id|q|id|q|t1.q-t2.q+t3.q 1|40|1|30|1|20|30 2|50|2|20|2|20|50 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems ________________________________ From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Fredrick Ughimi [fugh...@yahoo.com] Sent: Tuesday, January 31, 2012 11:20 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] A simple calculation, complex SQL Statement Hello, I have this scenario. Its an inventory Software I am updating. Finding better ways to do things. I have a main table (tblMainInventory), Sales table (tblSales) and a Receives table (tblReceives). The tblMainInventory is the base table while tblSales and tblReceives interacts with tblMainInventory to always arrive at the quantity in stock at any given time. Lets say I have: tblMainInventory (ProductNo1) = 40 tblSales (ProductNo1) = 30 tblReceives (ProductNo1) = 20 Balance (ProductNo1) = 30 Now, I want to be able to use the SQL statement to do the above calculation for all the products available (Not just ProductNo1 alone) in one SQL Statement. Is it possible? Best regards, ------------------------------------ Fredrick Ughimi, President Mega-Net Computers (Software Development Company) www.ughimisoft.com<http://www.ughimisoft.com/> supp...@ughimisoft.com fugh...@yahoo.com Phone Nos: 07035682478, 07055630187 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
