Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    1

    Unanswered: multiple fields in SELECT WHERE IN

    Hi All

    I'm in the process of trying to write a query (in access 2000) that copys data between tables, then updates related tables.

    The problem:

    One of the tables is linked on 2 fields, and I need to do a where not in query, so I can't use a join.

    Is it possible to do something along the lines of:

    select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

    Any help much appreciated

    Mark Middlemist

  2. #2
    Join Date
    Jan 2002
    Posts
    1
    YES YOU CAN

    select * from table1 where field1, field2 not in (select ref1, ref2 from table2)

    this works fine, but check that field1 and ref1 should have same datatype etc.,

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    This won't be upgradable to sql server though.
    You could

    select * from table1
    where not exists
    (select * from table2 where table1.field1 = table2.ref1 and table1.field2 = table2.ref2)


    or

    select table1.*
    from table1 left outer join table2
    on table1.field1 = table2.ref1 and table1.field2 = table2.ref2
    where table1.field1 is null

    these will still bring back results if the fields allow null whereas the in clause may well not.
    In all cases the results may not be correct for nullable fields.

Posting Permissions

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