RE:

From: "tamsin" <tg_mail(at)bryncadfan(dot)co(dot)uk>
To: <pgsql-general(at)postgresql(dot)org>
Subject: RE:
Date: 2001-06-29 12:40:04
Message-ID: NEBBKHBOBMJCHDMGKCNJGEFFDAAA.tg_mail@bryncadfan.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql

i think performance is better in postgres if you rewrite this sort of query
as:

select * from purchasemaster where exists (select 1 from itemmaster where
itempriority = 2 and transactionid = purchasemaster.transactionid)

hope this helps,
tamsin

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Kapil Tilwani
Sent: 28 May 2001 12:27
To: pgsql-general(at)postgresql(dot)org; pgsql-sql(at)postgresql(dot)org
Subject: [GENERAL]

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 transactionid
from itemmaster where itempriority = 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.,
greater than 4 , the ideal database would be Postgres

Only the problem is ... (correct me if i am wrong, PLEEEEEEEEEEEASE) that
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)",
etc. in place of the sub-query and accordingly, execute the main query.

Thank you,
Kapil

In response to

  • at 2001-05-28 11:26:37 from Kapil Tilwani

Responses

  • Re: at 2001-06-29 15:58:11 from Thomas T. Veldhouse

Browse pgsql-general by date

  From Date Subject
Next Message Fabrizio Mazzoni 2001-06-29 13:47:01 I: Help with indexes/queries/msaccess
Previous Message Jan Wieck 2001-06-29 12:31:56 Re: Triggers do the error checking

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2001-06-29 14:43:00 Re: day difference
Previous Message Wei Weng 2001-06-29 00:59:03 Re: restoring a dump