Re: Select value not in field
>Date: Thu, 3 Feb 94 8:38:20 EST
>From: Anbarnes@letterkenn-emh1.army.mil
>Subject: Select value not in field
>X-Informix-List-Id: <list.3463>
>Could someone help me with the SQL (NOT 4GL) syntax to select all
>values between x and y which are not in fieldz? For instance, fieldz
>contains all the numbers currently in use on tape labels. I want to
>see which numbers are NOT currently in use, within the available range
>of 0001-9999. The syntax totally escapes me!
I'd guess from your description, that this meets your criteria:
SELECT Col01
FROM Tab01
WHERE Col01 BETWEEN x AND y
AND Col01 NOT IN (SELECT FieldZ FROM Tab02);
If Tab02.FieldZ contains values 3, 7, 8, and Tab01.Col01 contains 1, 3, 5,
6, 7, 8, 10, 11 and x is 0 and y is 20, then this simply generates the list
1, 5, 6, 10, 11.
However, if you are asking for all the values in the range 0..20 except 3,
7, 8, then you are going to need to build a complete list of values 0..20
in a (temp) table, and then use the query above with Tab01 being the name
of your (temp) table.
You might care to read the articles in by Andrew Warden in C J Date's book
"Relational Database: Selected Writings 1985-1989" on the subject of
Chivalry.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>