Received: from veldy.net (w028.z064001117.msp-mn.dsl.cnc.net [64.1.117.28]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5TFxaa25054 for ; Fri, 29 Jun 2001 11:59:36 -0400 (EDT) (envelope-from veldy@veldy.net) Received: from HP2500B (localhost.veldy.net [127.0.0.1]) by veldy.net (Postfix) with SMTP id 1F7F2BAAC; Fri, 29 Jun 2001 10:59:32 -0500 (CDT) Message-ID: <003501c100b4$4ea2cd10$3028680a@tgt.com> From: "Thomas T. Veldhouse" To: "tamsin" , "PostgreSQL-general" References: Subject: Re: Date: Fri, 29 Jun 2001 10:58:11 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0032_01C1008A.6378C2F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200106/1225 X-Sequence-Number: 11859 This is a multi-part message in MIME format. ------=_NextPart_000_0032_01C1008A.6378C2F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable select pm.* from purchasemaster pm inner join itemmaster im on pm.transactionid =3D im.transactionid where im.itempriority =3D 2 Try this instead. Make sure the appropriate indexes (on pm.transactionid, im.transactionid an= d im.itempriority) exist if the tables are of any appreciable size.=20=20 Tom Veldhouse veldy@veldy.net ----- Original Message -----=20 From: tamsin=20 To: pgsql-general@postgresql.org=20 Sent: Friday, June 29, 2001 7:40 AM Subject: RE: [GENERAL]=20 i think performance is better in postgres if you rewrite this sort of que= ry as: select * from purchasemaster where exists (select 1 from itemmaster where= itempriority =3D 2 and transactionid =3D purchasemaster.transactionid) hope this helps, tamsin -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@po= stgresql.org]On Behalf Of Kapil Tilwani Sent: 28 May 2001 12:27 To: pgsql-general@postgresql.org; pgsql-sql@postgresql.org Subject: [GENERAL]=20 Hi, I have two tables purchasemaster (Master) and ItemMaster (child) linked= through TransactionID I have a query like select * from purchasemaster where transactionid in (select transaction= id from itemmaster where itempriority =3D 2) ****I need this query for MS-Access, MS-SQL and Postgres. Because the = application is supposed to be such that for upto 2-3 users, the application= would be running on MS-Access or MSDE, while for heavier databases i.e., g= reater than 4 , the ideal database would be Postgres Only the problem is ... (correct me if i am wrong, PLEEEEEEEEEEEASE) t= hat for each tuple in purchasemaster the sub-query is re-evaluated (True or= False????). What I am looking at is whether it is possible to execute the= query in the sub-query just once, get its value as in "(1,2,5,23,7,90)", e= tc. in place of the sub-query and accordingly, execute the main query. Thank you, Kapil ------=_NextPart_000_0032_01C1008A.6378C2F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
select pm.*
from purchasemaster pm
&= nbsp;=20 inner join itemmaster im
    on pm.transactionid =3D=20 im.transactionid
where im.itempriority =3D 2
 
Try this instead.
 
Make sure the appropriate indexes (on=20 pm.transactionid, im.transactionid and im.itempriority) exist if the t= ables=20 are of any appreciable size. 
 
Tom Veldhouse
veldy@veldy.net
 
----- Original Message -----
Fro= m:=20 tamsin
Sent: Friday, June 29, 2001 7:40 A= M
Subject: RE: [GENERAL]

i=20 think performance is better in postgres if you rewrite this sort of query= =20 as:
 
select * from purchasemaster where exists=20 (select 1 from itemmaster where itempriority =3D 2 and transactionid= =3D=20 purchasemaster.transactionid)
 
hope=20 this helps,
tamsin
 
-----Original Message-----
From: pgsql-general-owner@= postgresql.org=20 [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Kapil=20 Tilwani
Sent: 28 May 2001 12:27
To:=20 pgsql-general@postgresql.org; pgsql-sql@postgresql.org
Subject:=20 [GENERAL]

Hi,
 
I have two tables purchasema= ster=20 (Master) and ItemMaster (child) linked through TransactionID
 
I have a query like
select * from purchasemaster= where=20 transactionid in (select transactionid from itemmaster where itempriori= ty =3D=20 2)
 
****I need this query for MS= -Access,=20 MS-SQL and Postgres.  Because the application is supposed to be su= ch=20 that for upto 2-3 users, the application would be running on MS-Access = or=20 MSDE, while for heavier databases i.e., greater than 4 , the ideal data= base=20 would be Postgres
 
Only the problem is ... = ;=20 (correct me if i am wrong, PLEEEEEEEEEEEASE) that for each tuple in=20 purchasemaster the sub-query is re-evaluated (True or False????). = What=20 I am looking at is whether it is possible to execute the query in the= =20 sub-query just once, get its value as in "(1,2,5,23,7,90)", etc. in pla= ce of=20 the sub-query and accordingly, execute the main query.
 
Thank you,
Kapil
------=_NextPart_000_0032_01C1008A.6378C2F0--