mulltiple column "not in" query

[icccapital]

Is it possible to perform a multiple column "not in" query in access?

I created the query

select * from climktvaluefromgroups c where (c.group, c.invdate,
c.marketvalue, c.clicode) not in (select t.group, t.invdate, t.marketvalue,
t.clicode from tempclimkt t) and I got the error:

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.

Is this not possible?

 

[Dorian]

You cannot do what you are attempting.
You MIGHT be able to use concatenation like this:

select * from climktvaluefromgroups c where (c.group & c.invdate &
c.marketvalue & c.clicode) not in (select t.group & t.invdate &
t.marketvalue &
t.clicode from tempclimkt t)

Or you might have to concatenate the columns in separate queries then read
those queries in another query.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[vanderghast]

IN is a short hand for EXISTS:

SELECT *
FROM somewhere
WHERE (a, b, c) NOT IN (SELECT (m, n, p) FROM somewhereElse)

can be rewritten as


SELECT *
FROM somewhere AS u
WHERE EXISTS( SELECT *
FROM somewhereElse AS v
WHERE u.a=v.m
AND u.b=v.n
AND u.c=v.p)



Note that the 'vectorial NOT IN' exists only in few SQL dialects; it does
not work in Jet 4.0, neither in MS SQL Server 2005.


Vanderghast, Access MVP

 

[vanderghast]

oops, IN matches EXISTS and NOT IN matches NOT EXISTS. I mixed both in the
previous message.


Vanderghast, Access MVP

 

[icccapital]

Both are great answers, I really appreciate it.

IN is a short hand for EXISTS:

SELECT *
FROM somewhere
WHERE (a, b, c) NOT IN (SELECT (m, n, p) FROM somewhereElse)

can be rewritten as


SELECT *
FROM somewhere AS u
WHERE EXISTS( SELECT *
FROM somewhereElse AS v
WHERE u.a=v.m
AND u.b=v.n
AND u.c=v.p)



Note that the 'vectorial NOT IN' exists only in few SQL dialects; it does
not work in Jet 4.0, neither in MS SQL Server 2005.


Vanderghast, Access MVP

 

[icccapital]

Yeah I had guessed that no problem.

Side note, as is to be expected, both of these suggestions works, but the
Exists/Not Exists was a faster query

 

[John W. Vinson]

Side note, as is to be expected, both of these suggestions works, but the
Exists/Not Exists was a faster query

That's useful to remember. I believe it's because EXISTS quits when it finds
the first match - there is at least one record which exists, so OK! - while IN
goes on through the entire list.