Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unhappy Unanswered: Use SQL to find values NOT in a table column...?

    I want to have a SQL statement that will find all the values within a given range that are not in a table's column (being an integer field)...

    I'd like to do this to be used as the basis for values for an unbound drop down list. The users are selecting a value that will be added to the table so I only want to present values that aren't already in the table.

    TIA

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    You need to write a query with a sub query based on the table with the values

    Code:
    SELECT fields FROM MainTable
    WHERE intvalue NOT IN (SELECT intvalue FROM SubTable)
    Hope that makes sense
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Sep 2003
    Posts
    3
    I was afraid of that...

    No way to do it w/o creating another table that has all the values in the desired range...?


    TIA!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no way that i know of without that extra table

    use an integers table (swampboogie calls it a numbers table)

    you'll be surprised how many other places you'll need it

    note, though, that you don't really need to load it up with all the numbers in the range you want

    i find that with only the digits 0 through 9, i can use CROSS joins to get as many numbers as i need

    holler if swampboogie's syntax doesn't do it for ya


    rudy
    http://r937.com/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •