Re: SQL help with EXISTS

Hi Vern,

I agree with you. I have see rare cases where using the same file in EXISTs and the main element makes sense, but it's always because of some sort of funky database design (customers who have sales reps, but sales reps are also customers, so they're all
in the same file, and you're trying to find sales reps that actually have customers -- something like that). In general, it doesn't make any sense (IMHO :-).


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

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 ....)

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.