I have two tables.
They have to be joined with an outer join, one contains stock, the other
pending sales. Clearly not all items will have pending sales so the join
must be outer.
SELECT s.StockID,(s.Qty - sum(o.Qty)) AS Quantity
FROM Stock s
LEFT JOIN Orders o ON (s.StockID = o.StockID)
The problem I have is that if an item has no orders, then "sum(o.Qty)"
returns as NULL, and anoyingly this makes "(s.Qty - sum(o.Qty))" also NULL.
What I need is for 20-NULL to be 20 so that the query returns available
stock, ie: all stock not allocated to an order.
This is "MySQL 4.1.4-gamma-nt" as I am sure that makes a diference. Go on,
tell me that MSSQL behaves diferently and that once again MySQL is
performing against standards.
Any help?
--
Jay
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Special thanks to the CF Community Suite Silver Sponsor - CFDynamics
http://www.cfdynamics.com
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:188987
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
