Never mind, found it.
SELECT s.StockID,(s.Qty - sum(IFNULL(o.Qty,0))) AS Quantity
FROM Stock s
LEFT JOIN Orders o ON (s.StockID = o.StockID)
Does the trick.
--
Jay
> -----Original Message-----
> From: James Smith [mailto:[EMAIL PROTECTED]
> Sent: 30 December 2004 12:13
> To: CF-Talk
> Subject: SQL Issue
>
> 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:188988
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
