From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-sql@postgresql.org
Subject: Re: help me...
Date: Sun, 09 Nov 2003 21:59:14 -0500
Message-ID: <m3wua9vshp.fsf@wolfe.cbbrowne.com> (raw)
References: <20031108060215.7557.qmail@web14605.mail.yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: help me...
In-Reply-To: <m3wua9vshp.fsf@wolfe.cbbrowne.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox