NOT...IN subqueries returning no records

  • Thread starter Thread starter Trimtrom
  • Start date Start date
T

Trimtrom

Hi

I have just written the following query:

SELECT portal11Jan.USER_NAME, portal11Jan.SUCCESS,
portal11Jan.LAST_SUCCESS, portal11Jan.FAIL, portal11Jan.LAST_FAIL,
portal11Jan.VALID_USER_NAME
FROM portal11Jan
WHERE user_name not in (select surname from managers);

It has started returning no records when I absolutely KNOW that there
are applicable records. A few days later I get the records.

Is it something to do with the size of the tables, or the complexity
of the query?

If anyone could give me some tips or useful links I would be grateful.

Many thanks,

Trimtrom
 
If there is a NULL in

(SELECT surname FROM managers )


then, no record is returned, since the expression


user_name NOT IN (SELECT surname FROM managers )



evaluates to NULL, in that case, and since this does not evaluate to true,
for any record, no record is kept.



Try:


WHERE user_name NOT IN (SELECT surname
FROM managers
WHERE NOT(surname IS NULL) )




Hoping it may help,
Vanderghast, Access MVP
 
Try modifying the subquery.

SELECT portal11Jan.USER_NAME, portal11Jan.SUCCESS,
portal11Jan.LAST_SUCCESS, portal11Jan.FAIL, portal11Jan.LAST_FAIL,
portal11Jan.VALID_USER_NAME
FROM portal11Jan
WHERE user_name not in
(SELECT surname FROM managers WHERE surname Is NOT NULL);

Or perhaps more efficiently

SELECT portal11Jan.USER_NAME
, portal11Jan.SUCCESS
, portal11Jan.LAST_SUCCESS
, portal11Jan.FAIL
, portal11Jan.LAST_FAIL
, portal11Jan.VALID_USER_NAME
FROM portal11Jan LEFT JOIN Managers
ON portal11Jan.User_Name = Managers_surname
WHERE Managers.surname Is Null

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Try modifying the subquery.

SELECT  portal11Jan.USER_NAME, portal11Jan.SUCCESS,
portal11Jan.LAST_SUCCESS, portal11Jan.FAIL, portal11Jan.LAST_FAIL,
portal11Jan.VALID_USER_NAME
FROM portal11Jan
WHERE user_name not in
   (SELECT surname FROM managers WHERE surname Is NOT NULL);

Or perhaps more efficiently

SELECT  portal11Jan.USER_NAME
, portal11Jan.SUCCESS
, portal11Jan.LAST_SUCCESS
, portal11Jan.FAIL
, portal11Jan.LAST_FAIL
, portal11Jan.VALID_USER_NAME
FROM portal11Jan LEFT JOIN Managers
ON portal11Jan.User_Name = Managers_surname
WHERE Managers.surname Is Null

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
.













- Show quoted text -
Click to expand...

OK That has solved the problem....

Simple when you know how. Many thanks.

Trimtrom
 
Back
Top