SQL "NOT IN" doesn't function as expected (refresh)

[Farkyss]

Updating this question with a refresh:

http://www.clanimperium.net/1.JPG
http://www.clanimperium.net/2.JPG
http://www.clanimperium.net/3.JPG
http://www.clanimperium.net/4.JPG
(capital JPG is important)

1 shows results of
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID;
(no WHERE clause at all - results as expected)

2 shows results of the subquery on it's own
SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER IS NOT NULL;
(results as expected)

3 shows results of
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID;
WHERE pd.SERIAL_NUMBER NOT IN ('999789');
(manual NOT IN clause - results as expected - 999789 excluded)

4 Shows results of the full query
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID, pa.DESCRIPTION
FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd ON
po.SALES_NUMBER=pd.SALES_NUMBER) INNER JOIN PART AS pa ON
pd.PART_ID=pa.PART_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);
(Results NOT as expected 999789 isn't excluded)


TRIMming the subquery doesn't help. VAL can't be used because serial_number
can be alphabetic. Essentially NOT IN doesn't work with a list generated from
a subquery. Could someone attempt to reproduce these results? Any comment
from Microsoft from official? Anyone with an alternative method? This whole
project is frozen until this is fixed.

 

[John Spencer]

So using TRIM on both Serial_Number fields does not work. THe following fails

SELECT po.PURCHASE_DATE
, pd.QTY
, pd.SERIAL_NUMBER
, pa.PART_ID
, pa.DESCRIPTION
FROM (PURCHASEORDER AS po INNER JOIN PORD AS pd
ON po.SALES_NUMBER=pd.SALES_NUMBER)
INNER JOIN PART AS pa ON pd.PART_ID=pa.PART_ID
WHERE Trim(pd.SERIAL_NUMBER) NOT IN
(SELECT Trim(SERIAL_NUMBER)
FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);

As a check have you tried something like the following to make sure the record
in the INSTALL table actually contains the value 999789.
SELECT SERIAL_NUMBER FROM INSTALL WHERE SERIAL_NUMBER = '999789'

An alternative query:
SELECT po.PURCHASE_DATE
, pd.QTY
, pd.SERIAL_NUMBER
, pa.PART_ID
, pa.DESCRIPTION
FROM ((PURCHASEORDER AS po
INNER JOIN PORD AS pd
ON po.SALES_NUMBER=pd.SALES_NUMBER)
INNER JOIN PART AS pa
ON pd.PART_ID=pa.PART_ID)
LEFT JOIN INSTALL as I
ON Pd.SERIAL_Number = I.Serial_Number
WHERE I.SerialNumber Is Null

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Farkyss]

SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

produces expected results whereas

SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

procudes an unexpected, empty result. FYI, the lookup for this field in the
INSTALL is

SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY
[SERIAL_NUMBER];

have also tried replacing that with this
SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER];
as the original lookup was made by access's 'insert a field from another
table' wizard
This produces the same incorrect results

 

[Farkyss]

The PORD table contains serial number/quantity lines in a PURCHASE ORDER
table (as each purchase order can be of several different items, each in need
of their own serial number)
The INSTALL table contains records of certificates sent as proof that a part
has been fitted by an engineer. The important part of that record is the
serial number.
When entering details of an install certificate, I limited the choice of
serial numbers to those that actually existed - this would reduce errors
later on,
The code listed below is a shorten version i need for a QUERY to pull out
details from Purchase Order/PORD/PART tables for items whose serial numbers
DO NOT appear in the INSTALL tables. This information basically tells us what
items have been sold that don't have installation certificates for

The links to JPG give test data and screenshots of the results

 

[Farkyss]

Did your values in the INSTALL table come from PORD via a lookup table?
That's the issue as I see it.

SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

produces expected results whereas

SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

procudes an unexpected, empty result. FYI, the lookup for this field in the
INSTALL is

SELECT [PORD].[PORD_ID], PORD.[SERIAL_NUMBER] FROM PORD ORDER BY
[SERIAL_NUMBER];

have also tried replacing that with this
SELECT PORD.[SERIAL_NUMBER] FROM PORD ORDER BY [SERIAL_NUMBER];
as the original lookup was made by access's 'insert a field from another
table' wizard
This produces the same incorrect results

So using TRIM on both Serial_Number fields does not work. THe following fails

[quoted text clipped - 77 lines]

project is frozen until this is fixed.
.

Farkyss,

I was having problems with some of the queries then I realized I had a typing
error. I was using 999789 in Install and 999879 in PORD. I'm using Acc 2003
on XP SP3.
Once I adjusted that an now am getting expected results using John Spencer's
query (orig)
Here are images from my tests:

My test files http://www.box.net/files#/files/0/f/38679574/1/f_397107264
John's Query SQL
http://www.box.net/files#/files/0/f/38679574/1/f_397107274
Result of John's query
http://www.box.net/files#/files/0/f/38679574/1/f_397107256

Also, I am able to run successfully
SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

and
SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

 

[Farkyss]

This alternative query doesn't work either. Believe it's because Access isn't
processing the values of SERIAL_NUMBER in the INSTALL table correctly since
they're looked-up

 

[Farkyss]

You can download the db with some junk data in from here:
http://www.clanimperium.net/testdb.accdb

Did your values in the INSTALL table come from PORD via a lookup table?
That's the issue as I see it.

SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

[quoted text clipped - 39 lines]

and
SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

No, I simply typed them in. That's part of why I suggested some table
structures or a sample database to help with solution.

 

[Farkyss]

Correct behaviour can be restored by changing the SERIAL_NUMBER in the
INSTALL table to be a plain text box rather than a LOOKUP table of values
from the PORD table. Upon changing, the values stored in the SERIAL_NUMBER
field suddenly change to some unrelated digits.

Since this behaviour is clearly incorrect, could someone with a MVP tag
please report it as a bug? SQL queries on a field should be returning the
field's value, not a reference number.

As for my db? I'll have to go with a textbox. It isn't the functionality it
SHOULD have been, but it'll do

You can download the db with some junk data in from here:
http://www.clanimperium.net/testdb.accdb

Did your values in the INSTALL table come from PORD via a lookup table?
That's the issue as I see it.

SELECT SERIAL_NUMBER from PORD pd WHERE pd.SERIAL_NUMBER = "999789";

[quoted text clipped - 39 lines]
and
SELECT SERIAL_NUMBER from INSTALL inst WHERE inst.SERIAL_NUMBER = "999789";

No, I simply typed them in. That's part of why I suggested some table
structures or a sample database to help with solution.

 

[Farkyss]

The 'lookup junk' was put in by Access itself with it's wizard. The
implementation of the form is -supposed- to be independant of the underlying
storage, otherwise it makes a mockery of the SQL standard. I will be making
effort in the future to completely avoid Access - it's a load of trash

 

[John Spencer]

Sorry you feel that way. The unfortunate fact is that what is displayed in
datasheet view of a query is not what is stored if you are using a field that
uses the lookup feature. That's why most of us avoid using the lookup field.

On forms you can use a combobox to display one value while actually storing
another value. So, I can have a combobox that stores a primary key from my
personnel table in a foreign key field of another table that lists family
members. That is proper use of a relational database.

The simple way to solve the problem would be to change your subquery so it
references the table that really contains the value you are looking for.
WIthout more detail, I would say the sub-query might look something like the
following.

SELECT T.TheSerialNumberField
FROM INSTALL INNER JOIN TheSourceTableForTheLookup as T
ON INSTALL.Serial_Number = T.SomeRelatedField

I was going to attempt to download your sample db, but for some reason I got a
404 error.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[vanderghast]

That is not a bug. In fact, I explicitly asked you the question, the 25th of
February, and you answered it was NOT a look-up value, but JUST a value
being forced to be in another table:

==================
In the INSTALL table, the serial_number field was a "field from another
table" which means it is restricted to values of serial_number from a PART
table. It isn't a look-up in any other sense, it's just a field with
restricted values it can take.
==================

But clearly, it is a look-up value, and the information then given was quite
appropriate, if you would have identified that it was a look-up value:

================
if you have


Table1
serialNumber lookup value
1010 joe
1011 joe


Table2
serialNumber lookup value
1011 joe


then, if you use the un-lookup value, 1010, it is not in table2, and you
get, trough lookup, that joe is not in table2 !

Be sure that the lookup value is not leading you in the wrong conclusion.
Jet always process its comparisons/tests on the native, not -lookup, values.
================






Vanderghast, Access MVP