Re: NOT IN operator not working with subquery.

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 18:50:45 -0400

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
   On C.[ID#] = Invoices.[Link ID]
WHERE Invoice.[Link ID] is Null

Terry wrote:
>
> 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