>Can the "IN" clause, and a subquery, be used with multiple fields? I believe the answer is "no", but I wanted to confirm this.
>
>Example with one field:
>

>select * from MainTable where KeyField in (select KeyField in LookupTable)
>

>
>For two fields, I was thinking something along the lines of:
>

>select * from MainTable where (KeyField1, KeyField2) in (select KeyField1, KeyField2) in LookupTable
>

>
>But if I remember correctly, that resulted in an error.
>
>Assuming the answer is "no", and with respect to workarounds, I know I can use the "JOIN" instead; but how would I use the JOIN to emulate a NOT IN? I am pretty sure I can achieve this, but I am not sure about a more or less elegant way of doing this.
>
>Regards,
>
>Hilmar.

You can achieve NOT IN with LEFT JOIN and then check in WHERE clause for some field to be NULL

select *
from MainTable 
LEFT JOIN LookupTable ON MainTable.KeyField1 = LookupTable.KeyField1 AND MainTable.KeyField2 = LookupTable.KeyField2
WHERE LookupTable.PKField IS NULL