X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 65E563A58AE for ; Fri, 3 Dec 2004 21:33:52 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 54572-01 for ; Fri, 3 Dec 2004 21:33:42 +0000 (GMT) Received: from mail.99main.com (mail.99main.com [208.28.184.11]) by svr1.postgresql.org (Postfix) with ESMTP id 735543A5637 for ; Fri, 3 Dec 2004 21:33:42 +0000 (GMT) Received: from 99main.com (localhost [127.0.0.1]) by mail.99main.com (8.12.8+Sun/8.12.8) with ESMTP id iB3LXeMl022064 for ; Fri, 3 Dec 2004 16:33:40 -0500 (EST) From: "Keith Worthington" To: "PostgreSQL SQL" Reply-To: KeithW@narrowpathinc.com Subject: sum query Date: Fri, 3 Dec 2004 16:33:40 -0500 Message-Id: <20041203213340.M8095@narrowpathinc.com> X-Mailer: Open WebMail 1.70 20020717 X-OriginatingIP: 68.235.22.242 (npkeithw) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/32 X-Sequence-Number: 19652 Hi All, I am trying to join three tables and sum the quantities. The first table contains all of the possible items. The second table contains orders and the third table contains the items on each order. For all items found in tbl_item I need the total quantity on open orders. If an item is not on any open order the quantity should be zero. Can someone please explain the best way to do this? tbl_item id | ... ------+... AB12 | ... CD34 | ... EF34 | ... GH12 | ... JK56 | ... tbl_order order | closed |... ------+--------+... 1 | false |... 2 | true |... 3 | true |... 4 | false |... 5 | false |... tbl_item order | id | quantity ------+-------+--------- 1 | AB12 | 10 1 | CD34 | 5 2 | CD34 | 3 3 | EF34 | 2 3 | GH12 | 20 4 | GH12 | 4 5 | AB12 | 5 id | quantity ------+--------- AB12 | 15 CD34 | 5 EF34 | 0 GH12 | 4 JK56 | 0 Kind Regards, Keith ______________________________________________ 99main Internet Services http://www.99main.com