X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 805CDD1B541 for ; Mon, 10 Nov 2003 03:18:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 05099-09 for ; Sun, 9 Nov 2003 23:17:59 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id E43F8D1B545 for ; Sun, 9 Nov 2003 23:17:55 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id hAA3HtNw089839 for ; Mon, 10 Nov 2003 03:17:55 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id hAA34Kfd088084 for pgsql-sql@postgresql.org; Mon, 10 Nov 2003 03:04:20 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.sql Subject: Re: help me... Date: Sun, 09 Nov 2003 21:59:14 -0500 Organization: cbbrowne Computing Inc Lines: 53 Message-ID: References: <20031108060215.7557.qmail@web14605.mail.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:1VU8/+o+Lc3kn47ZHc2SEfwptPY= To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/78 X-Sequence-Number: 15893 The world rejoiced as ron_tabada@yahoo.com (ron_tabada) wrote: > Hello everyone, Good day! Could anyone help me translate this query > in Microsoft Access to Postgresql. I'm having a difficulty. Pls... > > Query1: > SELECT items.description, Sum(supplieditems.qty) AS SumOfqty > FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno > GROUP BY items.description; > > Query2: > SELECT [items].[description], Sum([customer].[qty]) AS SumOfqty > FROM (items INNER JOIN OtherItem ON [items].[itemno]=[OtherItem].[Itemno]) INNER JOIN customer ON > [OtherItem].[Itemno]=[customer].[itemcode] > GROUP BY [items].[description]; > > Query3: > SELECT [Query1].[SumOfqty], [Query2].[SumOfqty], [Query1]![SumOfqty]-[Query2]![SumOfqty] AS remain > FROM Query1, Query2; > > I have translated Query1 and Query2 in POSTGRESQL but I don't know > how to implement Query3. Apparently you have discovered the nearest equivalent to "VIEWs" in Access. I can suggest two ways: 1. Define "query1" and "query2" as PostgreSQL views, as with... create view query1 as SELECT items.description, Sum(supplieditems.qty) AS SumOfqty FROM items INNER JOIN supplieditems ON items.itemno = supplieditems.itemno GROUP BY items.description; create view query2 as [omitted details]. Query 3 should work perfectly well when it has the two views to work with. 2. Subselects... SELECT Query1.SumOfqty, Query2.SumOfqty, Query1.SumOfqty-Query2.SumOfqty AS remain FROM (select stuff for query 1) as query1, (select stuff for query 2) as query2; Approach #1. seems more appropriate, as it uses the views to keep the queries all simple. -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://www.ntlug.org/~cbbrowne/nonrdbms.html Incrementally extended heuristic algorithms tend inexorably toward the incomprehensible.