No title

A real (equi) join is not possible because the query what I posted on the
mailing list was WRONG... sheesh... sorry
what i meant was :

select * from purchasemaster where transactionid NOT in (select
transactionid from itemmaster where itempriority = 2)

Thanx,

----- Original Message -----
From: Randal L. Schwartz <merlyn@stonehenge.com>
To: Kapil Tilwani <karan_pg_2@yahoo.com>
Sent: Friday, June 29, 2001 7:04 PM
Subject: Re: [GENERAL]

why not just do a real join?

select a, b, c, from from purchasemaster, itemmaster
where purchasemaster.transactionid = itemmaster.transactionid
and itemmaster.itempriority = 2

don't fight SQL. :)

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777

0095

<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/&gt;
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl

training!

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

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

Kapil,

I have a query like
select * from purchasemaster where transactionid in (select
transactionid from itemmaster where itempriority = 2)

That's a fine query. You could even take it to meet your relatives.
:-)

****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

1. Why? In what way is MS Access or MSDE better for a 2-3 user
database? You may wish to re-evaluate your development strategy.

2. You're into a world of hurt, my friend. MSDE has a number of SQL
compliance lapses; MS Access is not SQL92 compliant at all, except for
the simplest SELECT queries. (I say this as someone who gets paid to
develop MS Access/VB)

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.

Depends on which database you're talking about. PostgreSQL, and, in
theory, MSDE, will only evaluate the IN() expression once (it's
correlated sub-selects that get evaluated multiple times -- and you
can't do these in Access at all).

Access, however, lacks a temporary table space. SInce it has to
evaluate the sub-select entirely in RAM, it evaluates it for every row
in the main table, unless both tables are quite small. As a result,
IN() queries run like molasses in Access. And Access doesn't support
any alternate subselect structures at all.

This does bring up an interesting question for Tom and Stephan:

Which is more efficient, under what circumstances?

1. SELECT a.* FROM a WHERE a.1 IN (SELECT b.1 FROM b WHERE b.2 = 0);

2. SELECT a.* FROM a WHERE EXISTS (
SELECT b.1 FROM b WHERE b.2 = 0 AND b.1 = a.1);

3. SELECT a.* FROM a JOIN
(SELECT b.1 FROM b WHERE b.2 = 0) sub_b
ON a.1 = sub_b.1;

I do a lot of #3 because it's easier to return calculated expressions
from the sub-query that way, but am I shooting myself in the foot,
performance-wise?

-Josh Berkus

P.S. Kapil, you might want to consider buying O'Reilly's "SQL in a
Nutshell" for cross-platform SQL comparisons.

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco

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@veldy.net

----- Original Message -----
From: tamsin
To: pgsql-general@postgresql.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@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

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@veldy.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@veldy.net

----- Original Message -----
From: tamsin
To: pgsql-general@postgresql.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@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

I agree entirely. On all the mailing lists that I am on (10+), not one
appreciates HTML, except the PostgreSQL lists, where I see a lot of it.
Since PostgreSQL primarily runs on *NIX, I thought most would prefer text.

Tom Veldhouse
veldy@veldy.net

----- Original Message -----
From: "Jan Wieck" <JanWieck@Yahoo.com>
To: "Thomas T. Veldhouse" <veldy@veldy.net>
Cc: "tamsin" <tg_mail@bryncadfan.co.uk>; "PostgreSQL-general"
<pgsql-general@postgresql.org>
Sent: Friday, June 29, 2001 12:33 PM
Subject: Re: [GENERAL]

Thomas T. Veldhouse wrote:

PS There is a lot of HTML email on this list. Is this generally

considered good? Or should I convert my replies to text?

Thomas T. Veldhouse wrote:

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

This is generally considered annoying, as lines >80
characters are :-P

Wasn't meant offensive - just couldn't resist.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

"Thomas T. Veldhouse" <veldy@veldy.net> writes:

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

This is generally considered evil. Plain text messages are preferred.

HTML is harder to quote when replying, and the variants that send both
HTML and plain text bloat the archives far more than necessary.

regards, tom lane

Thomas T. Veldhouse wrote:

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

This is generally considered annoying, as lines >80
characters are :-P

Wasn't meant offensive - just couldn't resist.

Let's not forget email with no subject, like this one.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Fri, Jun 29, 2001 at 12:27:40PM -0500, some SMTP stream spewed forth:

I agree entirely. On all the mailing lists that I am on (10+), not one
appreciates HTML, except the PostgreSQL lists, where I see a lot of it.
Since PostgreSQL primarily runs on *NIX, I thought most would prefer text.

With what are you agreeing?
I appreciate HTML. I have to; it's my job. HTML e-mail is Whole Other
Vile Beast.
I generally consider HTML email an annoyance, and, honestly, most of
the time I ignore it. I feel like HTML does not belong in the "message"
part of an e-mail. If an HTML file needs to be attached, so be it, I
encourage that even. Especially in a public forum, the essence of what
you are communicating should be the focus, not the layout or the pretty
colors or (Universe forbid) the images.

gh

Bruce Momjian wrote:

Thomas T. Veldhouse wrote:

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

This is generally considered annoying, as lines >80
characters are :-P

Wasn't meant offensive - just couldn't resist.

Let's not forget email with no subject, like this one.

Come on, let's create some mega-off-topic thread about if
"Re: [GENERAL]" is a subject or not. :-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com