mulltiple column "not in" query

  • Thread starter Thread starter icccapital
  • Start date Start date
I

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?
 
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".
 
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
 
oops, IN matches EXISTS and NOT IN matches NOT EXISTS. I mixed both in the
previous message.


Vanderghast, Access MVP
 
Both are great answers, I really appreciate it.

vanderghast said:
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
Click to expand...
 
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
 
Side note, as is to be expected, both of these suggestions works, but the
Exists/Not Exists was a faster query
Click to expand...

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.
 
Back
Top