how to use a composite key with the IN keyword in MS Access

Question

I want to write the following (pseudo) SQL statement in MS Access:

Select C 
from MyTable
where (A, B) IN (select distinct A,B from MyTable);

I tried that but got the complaint "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause."

I appreciate any feedback.

Answer 1

You can use an inner join as a filter:

select  c
from    MyTable t1
inner join
        (
        select  distinct
                a
        ,       b
        from    OtherTable
        ) t2
on      t1.a = t2.a
        and t1.b = t2.b

(I'm assuming you have two tables because the query doesn't make much sense for one table. Obviously, all combinations of A and B that are in Table1 will "also" be in Table1.)