Re: NOT IN operator not working with subquery.
From: Terry (ttrapp.spam.me.not_at_org.insurors)
Date: 10/27/04
- Next message: burg: "RE: Summing certain groups of values on a report"
- Previous message: ChuckW: "Summing certain groups of values on a report"
- In reply to: Michel Walsh: "Re: NOT IN operator not working with subquery."
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 27 Oct 2004 10:00:17 -0500
Michel,
That was it exactly. Thank you so much.
-- Terry
"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:uTozZ5AvEHA.1308@TK2MSFTNGP09.phx.gbl...
> 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" <ttrapp.spam.me.not@org.insurors> wrote in message
> news:U0zfd.38$YE4.7@fe61.usenetserver.com...
> >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
> >
> >
> >
>
>
- Next message: burg: "RE: Summing certain groups of values on a report"
- Previous message: ChuckW: "Summing certain groups of values on a report"
- In reply to: Michel Walsh: "Re: NOT IN operator not working with subquery."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
