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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)