Re: SQL help with EXISTS

Hi Michael

My issue with the example is that it is using the same file both in the main statement and in the EXISTS test - that is also happening in your modified form.

There might be some uses for that, but it still feels a little strange.

I see the more general case - your modification - as this kind of thing - the EXISTS SELECT is over __file2__, while the main SELECT is over __file1__. (Underlines for emphasis)

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

It's making my head spin a little - in addition to the weirdness I'm having right now with some Web development! Oy!

Vern

On 12/6/2012 4:18 PM, Michael Naughton wrote:

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:



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.