multiple fields in SELECT WHERE IN [Archive]

View Full Version : multiple fields in SELECT WHERE IN

MMiddlemist

01-10-02, 07:15

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

ANUPAMA

01-10-02, 09:58

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.,

nigelrivett

01-18-02, 15:52

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.