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

[Farkyss]

Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?

 

[John Spencer]

Make sure you eliminate any NULLs in the sub-query.

WHERE pd.SERIAL_NUMBER NOT IN
(SELECT SERIAL_NUMBER
FROM INSTALL
WHERE Serial_Number is NOT NULL)

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

 

[Jerry Whittle]

SELECT DISTINCT Install.SERIAL_NUMBER
FROM Install LEFT JOIN PD
ON Install.SERIAL_NUMBER = PD.SERIAL_NUMBER
WHERE PD.SERIAL_NUMBER Is Null
ORDER BY 1;

Select Install.SERIAL_NUMBER
FROM Install LEFT JOIN PD

 

[Farkyss]

Tried this. Access still ignores the NOT IN clause

 

[Farkyss]

Not sure this will achieve what I want. This in mind, find below the full SQL
code:
SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID,
pa.DESCRIPTION, c.BUSINESS_NAME, l.BUILDING_NAME, l.STREET, l.LOCALITY,
l.POSTAL_TOWN, l.COUNTY, l.PHONE
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) INNER JOIN CUSTOMER AS c ON
po.CUSTOMER_ID=c.CUSTOMER_ID) INNER JOIN LOCATION AS l ON
c.BILLING_ADDRESS=l.LOCATION_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT INSTALL.SERIAL_NUMBER FROM INSTALL)
AND pd.SERIAL_NUMBER IS NOT NULL;

 

[John W. Vinson]

Big issue here trying to build a query that lists records whose serial number
DOES NOT appear in another table.
In SQL, would use the condition:
WHERE pd.SERIAL_NUMBER NOT IN (SELECT SERIAL_NUMBER FROM INSTALL)
Access 2007 ignores this caviat and displays all records
Have tried also building the query using the query wizard for finding
unmatched entries, displaying records from my table which do NOT appear in
the INSTALL table, matching on the SERIAL_NUMBER field.
Both methods fail. Any ideas?
I also have issue with the fact that many of standard SQL commands are just
simply not implemented in the SQL implementation in Access 2007 (such as
JOIN, JOIN USING) - is there a list of unsupported SQL commands anywhere?

The NOT IN syntax works fine for me. Could you post the actual complete SQL of
your query? Does it have other criteria, particularly OR criteria?

 

[Farkyss]

SELECT po.PURCHASE_DATE, pd.QTY, pd.SERIAL_NUMBER, pa.PART_ID,
pa.DESCRIPTION, c.BUSINESS_NAME, l.BUILDING_NAME, l.STREET, l.LOCALITY,
l.POSTAL_TOWN, l.COUNTY, l.PHONE
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) INNER JOIN CUSTOMER AS c ON
po.CUSTOMER_ID=c.CUSTOMER_ID) INNER JOIN LOCATION AS l ON
c.BILLING_ADDRESS=l.LOCATION_ID
WHERE pd.SERIAL_NUMBER NOT IN (SELECT INSTALL.SERIAL_NUMBER FROM INSTALL)
AND pd.SERIAL_NUMBER IS NOT NULL;

 

[vanderghast]

If Serial_Number are alphanumerical, can you try :


.... where TRIM(pd.serial_number) not in (select TRIM(serial_number) from
install) and ...


Vanderghast, Access MVP

 

[Farkyss]

The values for serial_number in the INSTALL table are looked up from PD, so
physically can't be any different

 

[Farkyss]

(but no, tried it and no difference)
Would note that my test case involves one record in the INSTALL table that
shares a serial_number with the PORD pd table, so I know that record should
be omitted.
If I remove the subquery from the NOT IN condition and instead replace it
with just a list ("<val1>","<val2>") where one of the values is this known
serial_number, then it triggers correctly, so is it maybe that Access 2007 is
not allowing the use of a subquery in a NOT IN clause?

 

[vanderghast]

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

 

[Farkyss]

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

 

[Farkyss]

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 :-(

 

[vanderghast]

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]

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.

 

[John Spencer]

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

 

[John W. Vinson]

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!

 

[Farkyss]

Typo error on copy/paste. Doesn't appear in code on database

 

[Farkyss]

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)

 

[John Spencer]

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