Query returns 0 for subsequent columns

Hi All,

I am working on the query below (having made great progress thanks to your
help) and have run into a result I do not understand.

When I run the query I get 0 for all columns when the committed column is 0.
If I edit the souce table of the first join so that the committed column is
nonzero suddenly the other columns return their values. What might cause this
behavior?

Original result:
CAB2-100 | 4 | 0 | 20
CAB2-1000 | 0 | 0 | 0

Result with modified source tables.
CAB2-100 | 4 | 0 | 20
CAB2-1000 | 1 | 3 | 5

Kind Regards,
Keith

SELECT items.id,
COALESCE(sales.sum, 0) AS committed,
COALESCE(purchases.sum, 0) AS on_order,
COALESCE(stock.quantity, 0) AS on_hand
FROM peachtree.tbl_item AS items
LEFT OUTER JOIN (
SELECT sales_order.tbl_line_item.item_id,
SUM(sales_order.tbl_line_item.quantity) as sum
FROM sales_order.tbl_line_item JOIN sales_order.tbl_detail
USING (number)
WHERE NOT sales_order.tbl_detail.closed
GROUP BY item_id
) AS sales ON (items.id = sales.item_id)
LEFT OUTER JOIN (
SELECT purchase_order.tbl_line_item.item_id,
SUM(purchase_order.tbl_line_item.quantity) as sum
FROM purchase_order.tbl_line_item JOIN
purchase_order.tbl_detail USING (po_number)
WHERE NOT purchase_order.tbl_detail.closed
GROUP BY item_id
) AS purchases USING (item_id)
LEFT OUTER JOIN (
SELECT DISTINCT ON ( inventory.tbl_data.item_id )
inventory.tbl_data.item_id,
inventory.tbl_data.quantity
FROM inventory.tbl_data
ORDER BY inventory.tbl_data.item_id,
inventory.tbl_data.inventory_id DESC
) AS stock USING (item_id)
WHERE NOT items.inactive
ORDER BY item_id;

______________________________________________
99main Internet Services http://www.99main.com