That's what I said! :) Yasir On Sun, 9 Nov 2003, Christopher Browne wrote:
> Date: Sun, 09 Nov 2003 21:59:14 -0500 > From: Christopher Browne <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED] > Subject: Re: [SQL] help me... > > The world rejoiced as [EMAIL PROTECTED] (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. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
