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



This can also be done with an exception join which may be optimized
better (meaning it will run faster) than the sub query method shown
below. The general format of that is:

select file1.acctno from file1 
exception join file2 on file1.acctno = file2. acctno

You can also do this with a left outer join like this:

select file1.acctno from file1 
left outer join file2 on file1.acctno = file2. acctno
where file2.acctno is null

I find the exception join to be more clear than the left outer join but
they are the same functionally. All of this syntax is described in the
database manuals but those are not all that great for learning SQL from
scratch. There are better books for that. I think you will find some
listed if you search the archives.

Also, if you find that the query takes a long time to run (or you are
just curious to see what it's deciding to do under the covers), enter
STRDBG UPDPROD(*YES) on a command line before running the query in
STRSQL. When it completes, look at your job log and you'll see the
debugging messages from the query optimizer. If the query takes a long
time to run (or kills the system while it's running), use the CHGQRYA
command to set the max estimated time to zero. This will cause the query
optimizer to spit out the debug message but the query will not actually
run.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Buck
Sent: Wednesday, March 14, 2007 5:11 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: SQL HELP

I only want to select records that have
no matching number in subac00001. 

The general form is

select ... from ...
  where column not in
  (select subac00001 from table
     where...)

Basically, the inner select creates a list of subac00001 and the outer 
select checks to see that your key isn't in that list.
   --buck


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.