Multiple fields with NOT IN Subquery

  • Thread starter robert d via AccessMonster.com
  • Start date

R

robert d via AccessMonster.com

Can I use multiple fields for the Not In Subquery. For example:

WHERE FIELD1, FIELD2, FIELD3 NOT IN (SELECT FIELD4, FIELD5, FIELD6 FROM
TABLEA).

What I want is the combination of Field1, Field2, Field3 to not be in the
subquery. These three fields constitute the key.

Thanks.
 
Ad

Advertisements

D

Duane Hookom

One possible solution is
WHERE FIELD1 & FIELD2 & FIELD3 NOT IN (SELECT FIELD4 & FIELD5 & FIELD6 FROM
TABLEA)
 
M

Marshall Barton

robert said:
Can I use multiple fields for the Not In Subquery. For example:

WHERE FIELD1, FIELD2, FIELD3 NOT IN (SELECT FIELD4, FIELD5, FIELD6 FROM
TABLEA).

What I want is the combination of Field1, Field2, Field3 to not be in the
subquery. These three fields constitute the key.


No, you can't do that.

You can simulate that by using something like:
WHERE FIELD1 & "~" & FIELD2 & "~" & FIELD3 NOT IN (SELECT
FIELD4 & "~" & FIELD5 & "~" & FIELD6 FROM TABLEA)

But this kind of thing usually amounts to a frustrated outer
join (the wizard's unmatched query):

FROM tableB As B LEFT JOIN tableA As A
ON B.FIELD1 = A.FIELD4
AND B.FIELD2 = A.FIELD5
AND B.FIELD3 = A.FIELD6
WHERE A.FIELD4 Is Null
AND A.FIELD5 Is Null
AND A.FIELD6 Is Null
 
Ad

Advertisements

T

Tom Ellison

Dear Robert:

You can do this via a different mechanism from NOT IN. Use NOT EXISTS with
a correlated subquery instead:


WHERE NOT EXISTS(SELECT *
FROM TableA A
WHERE A.Field4 = X.Field1
AND A.Field5 = X.Field2
AND A.Field6 = X.Field3)

In the above, replace X with the name or alias of the table from which
Field1, Field2, and Field3 are supplied.

If you have trouble doing this, please supply the SQL of what you used and
the nature of the error message received, or other difficulty experienced.

Tom Ellison
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top