Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!samsung!dali.cs.montana.edu!uakari.primate.wisc.edu!zaphod.mps.ohio-state.edu!swrinde!elroy.jpl.nasa.gov!decwrl!mejac!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: need SQL for "WHat #'s < 1000 aren't in the table yet"
Message-ID: <25621@dice.la.locus.com>
Date: 20 Jun 91 23:59:19 GMT
References: <1991Jun19.202633.19582@spool.cs.wisc.edu>
Organization: Locus Computing Corp, Los Angeles
Lines: 52

In article <1991Jun19.202633.19582@spool.cs.wisc.edu> beverly@ai.cs.wisc.edu (Beverly Seavey) writes:
>
>  I need a SQL statement that will find the gaps in our numbering scheme,
>  e.
>  g.
>  something vaguely like
>
>	Find X such that X < 1000 and there does not exist a ref_ID = X


Simplest approach to this problem is to build an auxiliary table that
holds all the refIds from 1 to 1000 (this is pretty trivial).  Then
do:

      select refId from auxTable 
      where refId not in (select refId from mainTable)
        
This will do what you want.  If the list of refIds is too long for this
to be practical, I think there is a pretty weird join scheme that wil
do this, but I am still thinking about it.  Maybe I will solve it by
tomorrow.
 
Jon Rosen

PS -
 
Ahah! I have it!!! Boy is it UGLY!!!!!!

I believe the following will sort of give you what you want:
 
    select 'From ', refId+1 from mainTable
      where refId+1 not in (select refId from mainTable)
    union
    select 'To   ', refId-1 from mainTable
      where refId-1 not in (select refId from mainTable)
    order by 2,1
 
Test it out:  If mainTable has 1,3,4,5 and 9 in it,
the query should return:

    To    0
    From  2
    To    2
    From  6
    To    8
    From  10
 
I know it is ugly, but the info you need it there, right?
                  
:-) :-) :-)
 
Jon Rosen (totally grossed out that I came up with this answer :-)