NOT IN operator not working with subquery.

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I want to get a list of company names that we have in our database, that we
have not ever sent an invoice to. Why does the following query not work? I
have been banging my head on this for about an hour and can't figure it out.
Any help would be much appreciated.

SELECT [Companyinfo].[Name], [Companyinfo].[ID#] FROM [Companyinfo] WHERE
[Companyinfo].[ID#] NOT IN (SELECT [Link ID] FROM [Invoices]);

[Invoices].[Link ID] points to [Companyinfo].[ID#]

-- Terry
 
Don't know without more information on your table structure. What you've posted
says that it ought to work

You might try the following

SELECT C.[Name], C.[ID#]
FROM CompanyInfo as C LEFT JOIN Invoices
 
Access/Jet sometimes works better if you use (ansi 92) joins
instead of subqueries:


SELECT [Companyinfo].[Name], [Companyinfo].[ID#]
FROM [Companyinfo] left join [invoices]
on ([Companyinfo].[ID#] = [Link ID]) WHERE [Link id] is null;

(You need to put that through the query designer
to get the brackets and join direction right)

BTW, avoid using # in a field name if at all possible
(in fact, avoid using any punctuation). # is a
SQL delimiter, and is also the character Jet uses
to map "." in field names.

(david)
 
Hi,


It does not return any record? Probably there is a NULL in values returned
by the SELECT. Here is why.



x IN( a, b, c)

is the same as

x=a OR x=b OR x=c



x NOT IN(a, b, c)

is thus the same as

x <> a AND x <> b AND x <> c



If "c" is NULL, x <> NULL returns null, and thus, the last AND sequence
returns NULL. Now,

WHERE NULL

does not accept the record. Since that is the case for all records, all the
records are rejected.


In summary,

WHERE x NOT IN( SELECT ... )


will not return ANY record if the SELECT returns a NULL. With JET, if the
list is constant, rather than coming from a SELECT, the NULL is NOT
considered when the evaluation is performed.


? eval( " 4 NOT IN( NULL , 5, 6) " )
-1



but the "behavior" stands for a IN list from a SELECT.




Hoping it may help,
Vanderghast, Access MVP
 
Michel,
That was it exactly. Thank you so much.

-- Terry

Michel Walsh said:
Hi,


It does not return any record? Probably there is a NULL in values returned
by the SELECT. Here is why.



x IN( a, b, c)

is the same as

x=a OR x=b OR x=c



x NOT IN(a, b, c)

is thus the same as

x <> a AND x <> b AND x <> c



If "c" is NULL, x <> NULL returns null, and thus, the last AND sequence
returns NULL. Now,

WHERE NULL

does not accept the record. Since that is the case for all records, all the
records are rejected.


In summary,

WHERE x NOT IN( SELECT ... )


will not return ANY record if the SELECT returns a NULL. With JET, if the
list is constant, rather than coming from a SELECT, the NULL is NOT
considered when the evaluation is performed.


? eval( " 4 NOT IN( NULL , 5, 6) " )
-1



but the "behavior" stands for a IN list from a SELECT.




Hoping it may help,
Vanderghast, Access MVP


Terry said:
I want to get a list of company names that we have in our database, that we
have not ever sent an invoice to. Why does the following query not work? I
have been banging my head on this for about an hour and can't figure it
out.
Any help would be much appreciated.

SELECT [Companyinfo].[Name], [Companyinfo].[ID#] FROM [Companyinfo] WHERE
[Companyinfo].[ID#] NOT IN (SELECT [Link ID] FROM [Invoices]);

[Invoices].[Link ID] points to [Companyinfo].[ID#]

-- Terry
Click to expand...
Click to expand...
 
Thanks for the advice David. I knew there was a way to do it with a join,
but sometimes the joins make my head hurt ;) Thanks for showing me how.

I inherited this database from someone else. The use of reserved words and
other such evil in the table and column names just makes me cringe as well
:)

-- Terry




david epsom dot com dot au said:
Access/Jet sometimes works better if you use (ansi 92) joins
instead of subqueries:


SELECT [Companyinfo].[Name], [Companyinfo].[ID#]
FROM [Companyinfo] left join [invoices]
on ([Companyinfo].[ID#] = [Link ID]) WHERE [Link id] is null;

(You need to put that through the query designer
to get the brackets and join direction right)

BTW, avoid using # in a field name if at all possible
(in fact, avoid using any punctuation). # is a
SQL delimiter, and is also the character Jet uses
to map "." in field names.

(david)

Terry said:
I want to get a list of company names that we have in our database, that we
have not ever sent an invoice to. Why does the following query not work? I
have been banging my head on this for about an hour and can't figure it out.
Any help would be much appreciated.

SELECT [Companyinfo].[Name], [Companyinfo].[ID#] FROM [Companyinfo] WHERE
[Companyinfo].[ID#] NOT IN (SELECT [Link ID] FROM [Invoices]);

[Invoices].[Link ID] points to [Companyinfo].[ID#]

-- Terry
Click to expand...
Click to expand...
 
Back
Top