Replying to myself in the hopes that this will help someone else.
It turns out my JOIN condition was what was causing a problem.
I changed the following
AS purchases USING (item_id)
to
AS purchases ON (items.id = purchases.item_id)
and
AS stock USING (item_id)
to
AS stock ON (items.id = stock.item_id)
and the query started to return the desired results.
I can see that the ON clause is a much clearer specification but I do not
understand 1) Why the original USING clause didn't generate an error since
item_id wasn't available in items and 2) Why giving the committed section a
value suddenly caused the other values to be returned.
Keith
---------- Forwarded Message -----------
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: "PostgreSQL Novice" < pgsql-novice@postgresql.org>
Sent: Mon, 27 Dec 2004 13:04:03 -0500
Subject: 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;
------- End of Forwarded Message -------
Kind Regards,
Keith Worthington
President
Narrow Path, Inc.
520 Trumbull Highway
Lebanon, CT 06249-1424
Telephone: (860) 642-7114
Facsimile: (860) 642-7290
Mobile: (860) 608-6101
______________________________________________
99main Internet Services http://www.99main.com
