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

    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.


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

    SELECT fields FROM MainTable
    WHERE intvalue NOT IN (SELECT intvalue FROM SubTable)
    Hope that makes sense
    JJ Kennedy
    Double J IT Solutions

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

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

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


  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    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


Posting Permissions

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