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.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
