NOT IN (SQL) or Find Unmatched (+ other stuff)

Jet 4.0 has not changed, and unless there is a serious data corruption, it
should work. Note that Jet process its immediate NOT IN-list differently
than the NOT IN-sub query in case of nulls (in the list, or in the
sub-query), but that is a case of returning NO record, while your problem is
one about returning too many records (not returning none).


You said serial_number is a lookup value? Note that the 'un-lookup' value
is tested, not its result, so, 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

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.

I'm kinda at my whits end because I can't finish this job (3 days later) and
it's not because the code isn't right! I've tried it by raw SQL and tried it
using the Query builder. Both fail to work as described

"vanderghast" wrote:

Jet 4.0 has not changed, and unless there is a serious data corruption, it
should work. Note that Jet process its immediate NOT IN-list differently
than the NOT IN-sub query in case of nulls (in the list, or in the
sub-query), but that is a case of returning NO record, while your problem is
one about returning too many records (not returning none).


You said serial_number is a lookup value? Note that the 'un-lookup' value
is tested, not its result, so, 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

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 ('99789');
(manual NOT IN clause - results as expected - 99789 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 99789 isn't excluded)

I don't know how I can illustrate this any clearer :-(



Jet 4.0 has not changed, and unless there is a serious data corruption, it
should work. Note that Jet process its immediate NOT IN-list differently
than the NOT IN-sub query in case of nulls (in the list, or in the
sub-query), but that is a case of returning NO record, while your problem is
one about returning too many records (not returning none).


You said serial_number is a lookup value? Note that the 'un-lookup' value
is tested, not its result, so, 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

Ok, so serialNumber are strings. Can you give a try to:


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 val(pd.SERIAL_NUMBER) NOT IN (SELECT val(SERIAL_NUMBER) FROM INSTALL
WHERE
SERIAL_NUMBER IS NOT NULL);


(I added val( ) at two places)

and if that then works, it is probably because some serial_number values
have extra spaces or other non-printable character so they LOOK the same, on
display, but are different. Using val(string) returns the starting 'number'
:

? val("123a")
123


so it may correct the problem (and better than TRIM( ) would do). In other
words, I suspect the problem come from the DATA, not from Jet-SQL.


Vanderghast, Access MVP

Vanderghast, Access MVP


"Farkyss" wrote in message
...

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 ('99789');
(manual NOT IN clause - results as expected - 99789 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 99789 isn't excluded)

I don't know how I can illustrate this any clearer :-(



Jet 4.0 has not changed, and unless there is a serious data corruption,
it
should work. Note that Jet process its immediate NOT IN-list differently
than the NOT IN-sub query in case of nulls (in the list, or in the
sub-query), but that is a case of returning NO record, while your problem
is
one about returning too many records (not returning none).


You said serial_number is a lookup value? Note that the 'un-lookup'
value
is tested, not its result, so, 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

The types are set string because they can have alphabetic characters in them.
Both fields are set string for this reason, so clearly VAL isn't going to
work.


"vanderghast" wrote:

Ok, so serialNumber are strings. Can you give a try to:


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 val(pd.SERIAL_NUMBER) NOT IN (SELECT val(SERIAL_NUMBER) FROM INSTALL
WHERE
SERIAL_NUMBER IS NOT NULL);


(I added val( ) at two places)

and if that then works, it is probably because some serial_number values
have extra spaces or other non-printable character so they LOOK the same, on
display, but are different. Using val(string) returns the starting 'number'
:

? val("123a")
123


so it may correct the problem (and better than TRIM( ) would do). In other
words, I suspect the problem come from the DATA, not from Jet-SQL.


Vanderghast, Access MVP

Vanderghast, Access MVP


"Farkyss" wrote in message
...

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 ('99789');
(manual NOT IN clause - results as expected - 99789 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 99789 isn't excluded)

I don't know how I can illustrate this any clearer :-(



Jet 4.0 has not changed, and unless there is a serious data corruption,
it
should work. Note that Jet process its immediate NOT IN-list differently
than the NOT IN-sub query in case of nulls (in the list, or in the
sub-query), but that is a case of returning NO record, while your problem
is
one about returning too many records (not returning none).


You said serial_number is a lookup value? Note that the 'un-lookup'
value
is tested, not its result, so, 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

Perhaps it is just a typing error on your part, but Access does not work well
with extra semi-colons in queries.

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; REMOVE ;
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);

More importantly:
Since query 2 returns 999789 and NOT 99789 I would not expect 99789 to be
excluded from the results of query 4.

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

Farkyss wrote:
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 ('99789');
(manual NOT IN clause - results as expected - 99789 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 99789 isn't excluded)

I don't know how I can illustrate this any clearer :-(

On Fri, 26 Feb 2010 01:58:01 -0800, Farkyss
wrote:

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 99789 isn't excluded)

I'll try to find some time to replicate this on my 2007 box but... have you
tried a "frustrated outer join" query as an alternative to NOT IN? It can be
more efficient and should bypass the bug:

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 ON pd.SERIAL_NUMBER =
INSTALL.SERIAL_NUMBER
WHERE INSTALL.SERIAL_NUMBER IS NULL;

The other problem I see is that there is a semicolon before your WHERE clause
- I would have expected this to give you a syntax error, but it's concievable
that it's just ignoring the WHERE clause!
--

John W. Vinson [MVP]

Typo error on copy/paste. Doesn't appear in code on database
"John Spencer" wrote:

Perhaps it is just a typing error on your part, but Access does not work well
with extra semi-colons in queries.

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; REMOVE ;
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL WHERE
SERIAL_NUMBER IS NOT NULL);

More importantly:
Since query 2 returns 999789 and NOT 99789 I would not expect 99789 to be
excluded from the results of query 4.

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

Farkyss wrote:
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 ('99789');
(manual NOT IN clause - results as expected - 99789 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 99789 isn't excluded)

I don't know how I can illustrate this any clearer :-(
.

John, the semi-colon was a typo in the note, not present in the code.
Could you explain what a frustrated join is, and why this would work, as the
results I get from trying your code aren't right at all.....

I'll try to find some time to replicate this on my 2007 box but... have you
tried a "frustrated outer join" query as an alternative to NOT IN? It can be
more efficient and should bypass the bug:

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 ON pd.SERIAL_NUMBER =
INSTALL.SERIAL_NUMBER WHERE INSTALL.SERIAL_NUMBER IS NULL;

The other problem I see is that there is a semicolon before your WHERE clause
- I would have expected this to give you a syntax error, but it's concievable
that it's just ignoring the WHERE clause!
--

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 99789 isn't excluded)

DID you read the following?

=== QUOTE ===
More importantly:
Since query 2 returns 999789 and NOT 99789 I would not expect 99789 to be
excluded from the results of query 4.
=== End Quote ===

I based that comment on the jpegs you posted

The jpeg
http://www.clanimperium.net/2.JPG
shows one record with the serial number of 999789. Your posted query 3
eliminates 99789 with the where clause of
WHERE pd.SERIAL_NUMBER NOT IN ('99789');

Or is that another typo?

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