Using 2 or more "NOT IN" statements

  • Any suggestions for the following

    I'm able to run it with one NOT IN statement, but having trouble with the second.

    Should I create a temp table with both sets of data or is it possibly in a way like this?

    Assume

    tblNumbers has (1,2,3,4,5 & 6) for records

    tblNumA has (1,2) for records

    tblNumB has (3,4) for records

    I'd like to return 5 & 6 for the result

    ------------------------------------------------

    SELECT * FROM tblNumbers

    WHERE

    RecNum NOT IN (SELECT SomeNum FROM tblNumA)

    AND

    RecNum NOT IN (SELECT SomeNum FROM tblNumB)

    ------------------------------------------------

    This returns 0 results, but if i remove the "AND ......" it works fine for the first table, but I need to return the results using both tables.

    This will eventually end up as a "DELETE FROM ....." query

    Your suggestions are greatly appreciated.

    Rick

     

     

  • My Mistake,

    I had the same data in each table, returning 0 results.

    I've since added data NOT IN the other tables and it worked fine

  • DECLARE  @tblNumbers TABLE (ID int)

    INSERT INTO @tblNumbers VALUES (1)

    INSERT INTO @tblNumbers VALUES (2)

    INSERT INTO @tblNumbers VALUES (3)

    INSERT INTO @tblNumbers VALUES (4)

    INSERT INTO @tblNumbers VALUES (5)

    INSERT INTO @tblNumbers VALUES (6)

    DECLARE  @tblNumA TABLE(ID int)

    INSERT INTO @tblNumA VALUES (1)

    INSERT INTO @tblNumA VALUES (2)

    DECLARE  @tblNumB TABLE(ID int)

    INSERT INTO @tblNumB VALUES (3)

    INSERT INTO @tblNumB VALUES (4)

    SELECT * FROM @tblNumbers

    WHERE

    ID NOT IN (SELECT ID FROM @tblNumA UNION SELECT ID FROM @tblNumB)

     

  • I found that this also worked faster (suggestions/comments are welcome too)

    SELECT * FROM tblNumbers

    WHERE

    RecNum NOT IN

    (

    SELECT SomeNum FROM tblNumA

    UNION

    SELECT SomeNum FROM tblNumB

    )

     

     

  • Thanks SQL Junkie,

    Looks like the postings crossed paths

    Thanks for the reply

  • Just for kicks, here's an alternate method using JOINS:

    SELECT n.*

    FROM @tblNumbers n

    LEFT JOIN @tblNumA a

     ON n.ID = a.ID

    LEFT JOIN @tblNumB b

     ON n.ID = b.ID

    WHERE a.ID Is Null

      AND b.ID Is Null

    - Paul

    http://paulpaivasql.blogspot.com/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply