Re: SQL help with EXISTS

Thanks, Vern! I wasn't sure exactly how to refer to those.

Actually, it occured to me afterwards that the original statement might have thrown up some red flags. It's of the general form:

SELECT * FROM file1 a WHERE <some tests> AND EXISTS (SELECT * FROM file1 b WHERE b.field1=a.field1 and b.field2=a.field2 ....)

It seems to me that the rows found in the EXISTS clause will already have been found by the original SELECT -- at least before <some tests> -- so the EXISTS clause can't add anything .... right?

To make it useful, you'd have to do something like:

SELECT * FROM file1 a WHERE <some tests> AND EXISTS (SELECT * FROM file1 b WHERE b.field3=a.field1 and b.field4=a.field2 ....)

which would give you a different set of rows in EXISTS from the original SELECT.

Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:

Michael

You confirm my thoughts. I like that you presented the actual statement
to use - much simplified, eh?

I think, however, that it is confusing to speak of JOINing - there is no
joining here. At least not in either statement, whether in the style of
the old days or in the explicit JOIN syntax we have now. Why is there no
JOIN? Because each SELECT has exactly one table in the FROM clause.

A JOIN might be how it is implemented, but we are not specifying one
explicitly. the "where a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD"
is better known as a correlation, not JOIN criteria.

Regards
Vern

Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please notify us immediately by replying to it and then delete it from your computer.