Stock balance query

Since the actual database doesn't get updated and I'm not a sql expert, can I get assistance to get a list of products remaining balance by updating the sql below.Can't get the list to show stock with a 0 balance.

SELECT ProductStock,((SELECT ProductStock - coalesce(sum(DetailQuantity),0) AS NumLeft
FROM (orderdetails join orders on((orders.OrderID = orderdetails.DetailOrderID)))
WHERE ((orderdetails.DetailProductID = ProductID) and (orders.OrderDate > ProductUpdateDate)))) AS NumLeft,(SELECT CouponProductID FROM couponproducts WHERE couponproducts.ProductID = products.ProductID AND couponID = 41 LIMIT 1) AS CouponProductID,coalesce((select productoptions.GroupingID FROM productoptions where productoptions.ProductID = products.ProductID limit 1),products.ProductID) AS GroupingID FROM products WHERE ProductUnlimited = 0 AND ((SELECT ProductStock - coalesce(sum(DetailQuantity),0) AS NumLeft FROM (orderdetails join orders on((orders.OrderID = orderdetails.DetailOrderID))) WHERE ((orderdetails.DetailProductID = ProductID) and (orders.OrderDate > ProductUpdateDate))))

I'm nit sure i understand what it is you are asking.

the stock in the products table should be getting updated when a purchase is made, are you saying that is not happening?

I have created a support ticket so we can look into this issue further.

To view and edit your support ticket, please log into your support history:
supporthistory.php

If anyone else is experiencing this same issue, please append to this thread.

Sorry I wasn't clear.

The stock balance does get updated but not actually in the database.

My client is a service company who do repairs and sometimes uses the same stock. I have created additional pages in the admin to maintain their Technician work. One of the pages is to take parts from stock. I want to ensure that when they select a product/part from a dropdown list that there are sufficient item in stock.

I'm not a sql expert but was trying to modify your'll query to show the list of product but does show all the product.


If the quantity they select is in-sufficient, they get an error that says "The quantity you specified is not available in stock. The quantity field has been updated to reflect what is currently available."

update the support ticket that was created and we can look into the query with you.