× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



If the EXISTS subselect is correlated, ie.

select <> from FILEA A
where exists (select * from FILEB B where b.key = A.key)

Then the exists is evaluated whenever the correlated field(s) change.

Charles


On Thu, Dec 6, 2012 at 12:06 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

I don't think that is correct. It seems to do the subselect (return a set
of rows) and THEN join the result to the outer select. A few responses
have stated what you said - that EXISTS simply returns TRUE or FALSE. But
I think that it must do this for each row in the outer select in the case
where a join is occurring?

Maybe Im wrong, I don't know SQL and it is elusive and powerful and
complex so I don't understand all the subtleties.



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Thursday, December 06, 2012 11:01 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL help with EXISTS

The EXISTS says "are there any". You get the same results in the outer
select whether there's one or many results from the subselect.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Thursday, December 06, 2012 10:54 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL help with EXISTS

Thanks - you are correct - nothing to relate the outer query to the
subselect. I must have deleted it :(

Please look at my recent post - if I drop the GROUP BY, result will have
too many records I think - I only want ONE rec from outer select for each
occurrence in file.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan Kimmel
Sent: Thursday, December 06, 2012 10:50 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL help with EXISTS

You have nothing to relate the outer query to the subselect. Looks like
you deleted the A. in front of ASHPNBR in the where clause. I think it
should be:

select * from miscchg A
where
exists
(select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG where A.ASHPNBR =
'BN123456'
group by ASHPNBR,ABLDNGD, AMSCHRG )

By the way there's no need for all the fields in the subselect. You'd get
the same results quicker with:

select * from miscchg A
where
exists
(select 1 from MISCCHG where A.ASHPNBR = 'BN123456'
group by ASHPNBR,ABLDNGD, AMSCHRG )

I don't think the group by is buying you anything, either.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Thursday, December 06, 2012 10:23 AM
To: 'Midrange Systems Technical Discussion'
Subject: SQL help with EXISTS

I am pretty sure that the following selected only a few records from a
large file (ASHPNBR = 'BN123456' ).

Then I changed a paren or something small and I cant get it to select only
a few records - now it selects ALL values of ASHPNBR.

The sub-select works great and only selects a few records.

When I add the outer select, the entire file is selected (all ASHPNBR
values).

How does EXISTS know which keys to join between the inner & outer query?
Do I have to explicitly state the join? Or does "EXISTS" make assumption
about which fields to join?

Thanks







select * from miscchg A
where
exists
(select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG where ASHPNBR = 'BN123456'
group by ASHPNBR,ABLDNGD, AMSCHRG )

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________

______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.