pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tamsin <tg_mail@bryncadfan.co.uk>
To: pgsql-general@postgresql.org
Subject: RE:
Date: Fri, 29 Jun 2001 13:40:04 +0100
Message-ID: <NEBBKHBOBMJCHDMGKCNJGEFFDAAA.tg_mail@bryncadfan.co.uk> (raw)
In-Reply-To: <001f01c0e812$33a54b60$1300a8c0@kapil1>

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Kapil Tilwani
  Sent: 28 May 2001 12:27
  To: pgsql-general@postgresql.org; pgsql-sql@postgresql.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

view thread (7+ messages)  latest in thread

Message-ID: <NEBBKHBOBMJCHDMGKCNJGEFFDAAA.tg_mail@bryncadfan.co.uk>
Permalink:  ../NEBBKHBOBMJCHDMGKCNJGEFFDAAA.tg_mail@bryncadfan.co.uk/
Also on:    postgresql.org/message-id/NEBBKHBOBMJCHDMGKCNJGEFFDAAA.tg_mail@bryncadfan.co.uk

 · 

reply

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: tg_mail@bryncadfan.co.uk, pgsql-general@postgresql.org
  Subject: RE:
  In-Reply-To: <NEBBKHBOBMJCHDMGKCNJGEFFDAAA.tg_mail@bryncadfan.co.uk>

* 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