Re: Searching for deleted/skipped numbers
arcieri@pilot.njin.net (Joseph Arcieri) writes:
> Hi, I'm not sure if this is possible .... but .... I've created a database
> with tables that are joined by a unique id number. Sometimes we delete rows
> and would like to find unused id numbers to use again. Is there away to
> query an integer field for skips in a range? e.g. if I search a field of
> integers ranging from 1 to 100 can I find the unused numbers? Make sense?,
> think I'm repeating myself. Any directions or suggestions would be great!
> Thanks in advance, Joe Arcieri
>
Joseph,
You could try the following :-
SELECT int1
FROM table1
WHERE int1 NOT IN (
SELECT UNIQUE int2 FROM table2)
This will give you all rows in table 1 that do not have a corresponding
value in table2.
OR
SELECT int1,int2
FROM table1, OUTER table2
WHERE table1.int1 = table2.int2
INTO TEMP missing;
SELECT int1 from missing WHERE int2 IS NULL
This should achive much the same result
Regards
Steve
--
-----------------------------------------------------------------------------
EnsignData Ltd. - Informix and Tetra Accounting systems consultancy
+44 1634 577054
Steve Weet steve@weet.demon.co.uk