SELECT x from Table1 WHERE Not (x IN(SELECT x FROM Table2));

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables that have a field called [Product number](text, width 255).
Both tables contain entries in [Product number] that does not exist in the
other table. I want to be able to list exactly which product numbers these
are. So I use the queries:

SELECT [Product number]
FROM Software
WHERE not([product number] IN (Select [product number] FROM imported));

and vice versa. It doesn't work; the result is empty. The one listed above
did work the first time I tried it, and then magically stopped working
without me changing anything in it or any of the tables referenced by it. The
query where the tables switch roles never worked.

If I try to list the intersection of the sets of product numbers instead,
using the query

SELECT [Product number]
FROM Software
WHERE [product number] IN (Select [product number] FROM imported);

I *do* get the correct results. Is there anything wrong with my query
including "Not"?

Btw, I'm using Access 2000 (sp3) on Windows 2000 Professional. Jet version
is 4.0.

Thanks,

Daniel
 
use
SELECT [Product number]
FROM Software
WHERE [product number] NOT IN (Select [product number] FROM imported);

"Daniel" напиÑа:
 
Daniel said:
If I try to list the intersection of the sets of product numbers instead,
using the query

SELECT [Product number]
FROM Software
WHERE [product number] IN (Select [product number] FROM imported);

I *do* get the correct results. Is there anything wrong with my query
including "Not"?
Click to expand...

SELECT Software.[Product number]
FROM Software LEFT JOIN imported
ON Software.[Product number] = imported.[Product number]
WHERE imported.[Product number] IS NULL;

SELECT Software.[Product number]
FROM Software
WHERE NOT EXISTS
(
SELECT *
FROM imported
WHERE Software.[Product number] = imported.[Product number]
);
 
I tried that already but it doesn't work either. I shoul dhave mentioned
that, Thanks anyway :)

chonny said:
use
SELECT [Product number]
FROM Software
WHERE [product number] NOT IN (Select [product number] FROM imported);

"Daniel" напиÑа:
I have two tables that have a field called [Product number](text, width 255).
Both tables contain entries in [Product number] that does not exist in the
other table. I want to be able to list exactly which product numbers these
are. So I use the queries:

SELECT [Product number]
FROM Software
WHERE not([product number] IN (Select [product number] FROM imported));

and vice versa. It doesn't work; the result is empty. The one listed above
did work the first time I tried it, and then magically stopped working
without me changing anything in it or any of the tables referenced by it. The
query where the tables switch roles never worked.

If I try to list the intersection of the sets of product numbers instead,
using the query

SELECT [Product number]
FROM Software
WHERE [product number] IN (Select [product number] FROM imported);

I *do* get the correct results. Is there anything wrong with my query
including "Not"?

Btw, I'm using Access 2000 (sp3) on Windows 2000 Professional. Jet version
is 4.0.

Thanks,

Daniel
Click to expand...
Click to expand...
 
Thank you, both of your suggestions work perfectly :-)

I think I understand now why my query didn't work: After the second select
has returned it's results, the query is

SELECT [Product number]
FROM Software
WHERE [product number] IN (foo,bar,...);

whereas I need

SELECT [Product number]
FROM Software
WHERE [product number] IN ("foo","bar",...);

Is that it?
 
I have two tables that have a field called [Product number](text, width 255).
Both tables contain entries in [Product number] that does not exist in the
other table. I want to be able to list exactly which product numbers these
are. So I use the queries:

SELECT [Product number]
FROM Software
WHERE not([product number] IN (Select [product number] FROM imported));

and vice versa. It doesn't work; the result is empty. The one listed above
did work the first time I tried it, and then magically stopped working
without me changing anything in it or any of the tables referenced by it. The
query where the tables switch roles never worked.

If I try to list the intersection of the sets of product numbers instead,
using the query

SELECT [Product number]
FROM Software
WHERE [product number] IN (Select [product number] FROM imported);

I *do* get the correct results. Is there anything wrong with my query
including "Not"?

Btw, I'm using Access 2000 (sp3) on Windows 2000 Professional. Jet version
is 4.0.
Click to expand...


I think you may be confusing the query engine by not
qualifying the field name:

SELECT Software.[Product number]
FROM Software
WHERE Not Software.[product number] IN (Select
imported.[product number] FROM imported)

But, you will get much better performance by using a
frustrated outer join as peregenem posted. IN and EXISTS
commonly lose track of the table's indexes and might run an
order of magnitude slower.
 
Marshall said:
you will get much better performance by using a
frustrated outer join as peregenem posted. IN and EXISTS
commonly lose track of the table's indexes and might run an
order of magnitude slower.
Click to expand...

Thanks for the performance into. I prefer the OUTER JOIN syntax because
it makes more 'logical' sense to me!
 
Yes, you're right. It does work when I qualify the field name also in the
second SELECT. I don't quite understand why I have to do this, but evidently
it is so.

It is also clear that the outer join method is much faster just like you
say. Unlike peregeman I prefer the construct using Not In(...) from a logical
viewpoint, but I guess when you think about it it does make sense that this
method is slower, because the db engine has no way of knowing in advance how
to optimize a query like that.

Anyway, thanks a lot :-)
 
Back
Top