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
______________________________________________________________________
midrange.com
