Re: Query returns 0 for subsequent columns - Mailing list pgsql-novice

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