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