Re:

Heck, you could refine it this way as well.

select pm.*
from purchasemaster pm
inner join itemmaster im
on (pm.transactionid = im.transactionid and im.itempriority = 2)

This might reduce memory usage. I am not sure which will turn out to perform better in a resource friendly environment.

Tom Veldhouse
veldy(at)veldy(dot)net

PS There is a lot of HTML email on this list. Is this generally considered good? Or should I convert my replies to text?

----- Original Message -----
From: Thomas T. Veldhouse
To: tamsin ; PostgreSQL-general
Sent: Friday, June 29, 2001 10:58 AM
Subject: Re: [GENERAL]

select pm.*
from purchasemaster pm
inner join itemmaster im
on pm.transactionid = im.transactionid
where im.itempriority = 2

Try this instead.

Make sure the appropriate indexes (on pm.transactionid, im.transactionid and im.itempriority) exist if the tables are of any appreciable size.

Tom Veldhouse
veldy(at)veldy(dot)net

----- Original Message -----
From: tamsin
To: pgsql-general(at)postgresql(dot)org
Sent: Friday, June 29, 2001 7:40 AM
Subject: RE: [GENERAL]

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