Postgres Pro
Downloads
Home   >   mailing lists

Thread: help me...

help me...

From
ron_tabada
Date:
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.

Want to chat instantly with your online friends? Get the FREE Yahoo! Messenger

Re: help me...

From
Yasir Malik
Date:
Hi,
I'm usually wrong, but you can create a view for Query1 and Query2.  That
is do
create view Query1 [your query for query1];
create view Query2 [your query for query2];

Then what you are doing should work.  Are views offered in Access?
Regards,
Yasir

On Sat, 8 Nov 2003, [iso-8859-1] ron_tabada wrote:

> Date: Sat, 8 Nov 2003 06:02:15 +0000 (GMT)
> From: "[iso-8859-1] ron_tabada" <ron_tabada@yahoo.com>
> Reply-To: ronald@my.msuiit.edu.ph
> To: pgsql-sql@postgresql.org
> Subject: [SQL] help me...
>
> 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.
>
>
> ---------------------------------
> Want to chat instantly with your online friends?Get the FREE
Yahoo!Messenger

Re: help me...

From
Christopher Browne
Date:
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
supplieditemsON 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 remainFROM    (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.

Re: help me...

From
Yasir Malik
Date:
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 <cbbrowne@acm.org>
> To: pgsql-sql@postgresql.org
> Subject: Re: [SQL] help me...
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Re: help me...

From
Yasir Malik
Date:
Hi,
I'm usually wrong, but you can create a view for Query1 and Query2.  That
is do
create view Query1 [your query for query1];
create view Query2 [your query for query2];

Then what you are doing should work.
Regards,
Yasir

On Sat, 8 Nov 2003, [iso-8859-1] ron_tabada wrote:

> Date: Sat, 8 Nov 2003 06:02:15 +0000 (GMT)
> From: "[iso-8859-1] ron_tabada" <ron_tabada@yahoo.com>
> Reply-To: ronald@my.msuiit.edu.ph
> To: pgsql-sql@postgresql.org
> Subject: [SQL] help me...
>
> 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.
>
>
> ---------------------------------
> Want to chat instantly with your online friends?�Get the FREE Yahoo!Messenger