Not Exist Query

[rilkyn]

I've been trying to solve this problem on and off for a couple of days now but am getting nowhere. Hopefully someone can help.

I have two tables where five columns match.
Table one has 10 columns and table 2 has just the five columns

I am trying to run a query where the result shows all the records in table two that do not exist in table 1. The problem is that I am trying to match all five fields from table 2 to table 1. I have run a query against table one to pick out the records with only the five fields I am trying to match as this reduces the number of records from 78,000+ to just over 4,000

I have tried the following sql but am getting a nil return:

SELECT DISTINCT .DATA_GRP_ID, .DATA_GRP_VER, .ACCESS_ID, .FUNCTION, .FUNCTION_QUAL
FROM [Pure Gold Paths Table] AS B
WHERE NOT EXISTS
(SELECT [All Access Control Table Query].DATA_GRP_ID, [All Access Control Table Query].DATA_GRP_VER, [All Access Control Table Query].ACCESS_ID, [All Access Control Table Query].FUNCTION, [All Access Control Table Query].FUNCTION_QUAL
FROM [All Access Control Table Query]
WHERE .DATA_GRP_ID = [All Access Control Table Query].DATA_GRP_ID
AND .DATA_GRP_VER = [All Access Control Table Query].DATA_GRP_VER
AND .ACCESS_ID = [All Access Control Table Query].ACCESS_ID
AND .FUNCTION = [All Access Control Table Query].FUNCTION
AND .FUNCTION_QUAL = [All Access Control Table Query].FUNCTION_QUAL);

The nested select seems to work and gives me (i think) the correct number of matches. The records that don't match should, therefore be the remainder and equals about 40 records.

 

[GeertVerhoeven]

That's strange since the query seems to be correct.

Try this:
SELECT DISTINCT A.DATA_GRP_ID, A.DATA_GRP_VER, A.ACCESS_ID, A.FUNCTION, A.FUNCTION_QUAL
FROM [Pure Gold Paths Table] AS A
LEFT OUTER JOIN [Pure Gold Paths Table] AS B
ON A.DATA_GRP_ID = B.DATA_GRP_ID
AND A.DATA_GRP_VER = B.DATA_GRP_VER
AND A.ACCESS_ID = B.ACCESS_ID
AND A.FUNCTION = B.FUNCTION
AND A.FUNCTION_QUAL = B.FUNCTION_QUAL
WHERE B.DATA_GRP_ID IS NULL
AND B.DATA_GRP_VER IS NULL
AND B.ACCESS_ID IS NULL
AND B.FUNCTION IS NULL
AND B.FUNCTION_QUAL IS NULL

If needed, you can replace the WHERE clause by WHERE B.PrimaryKeyColumn IS NULL



Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[rilkyn]

Thanks for the quick response but unfortunately it does not work. I can't see any reference to the query on table 1 called [All Access Control Table Query]. The table is called [All Access Control Table]

When I ran the query the first record returned is held on table 2.

 

[GeertVerhoeven]

Sorry, it should have been like this:

SELECT DISTINCT A.DATA_GRP_ID, A.DATA_GRP_VER, A.ACCESS_ID, A.FUNCTION, A.FUNCTION_QUAL
FROM [Pure Gold Paths Table] AS A
LEFT OUTER JOIN [All Access Control Table Query] AS B
ON A.DATA_GRP_ID = B.DATA_GRP_ID
AND A.DATA_GRP_VER = B.DATA_GRP_VER
AND A.ACCESS_ID = B.ACCESS_ID
AND A.FUNCTION = B.FUNCTION
AND A.FUNCTION_QUAL = B.FUNCTION_QUAL
WHERE B.DATA_GRP_ID IS NULL
AND B.DATA_GRP_VER IS NULL
AND B.ACCESS_ID IS NULL
AND B.FUNCTION IS NULL
AND B.FUNCTION_QUAL IS NULL

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[rilkyn]

I tried this and still get records in the results that exist on the Access Control Table. Could this be a problem with the fact that empty fields are valid in the FUNCTION_QUAL column?

An example of a record that is being reported is

DATA_GRP_ID DATA_GRP_VER ACCESS_ID FUNCTION FUNCTION_QUAL
CD010 5 CA000 I


This record is on the Access Control table and should be excluded from the results

 

[GeertVerhoeven]

Ok, I think I get the problem. You need to watch out when comparing NULL values.

For example the following query will return 0.
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END

This means that NULL is not the same as another NULL. To avoid this, you must do something like

ISNULL(Field1, 'UNKNOWN') = ISNULL(Field2, 'UNKNOWN')





Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[rilkyn]

Thank you

I found the syntax is different for Access but once I got that right it worked perfectly. Here is the query if anybody else runs across a similar problem. The bit in bold relates to null values:

SELECT DISTINCT [Pure Gold Paths Table].DATA_GRP_ID, [Pure Gold Paths Table].DATA_GRP_VER, [Pure Gold Paths Table].ACCESS_ID, [Pure Gold Paths Table].FUNCTION, [Pure Gold Paths Table].FUNCTION_QUAL, [All Access Control Table].DATA_GRP_ID
FROM [All Access Control Table] RIGHT JOIN [Pure Gold Paths Table] ON (NZ([All Access Control Table].FUNCTION_QUAL, "NULL") = NZ([Pure Gold Paths Table].FUNCTION_QUAL, "NULL")) AND ([All Access Control Table].FUNCTION = [Pure Gold Paths Table].FUNCTION) AND ([All Access Control Table].ACCESS_ID = [Pure Gold Paths Table].ACCESS_ID) AND ([All Access Control Table].DATA_GRP_VER = [Pure Gold Paths Table].DATA_GRP_VER) AND ([All Access Control Table].DATA_GRP_ID = [Pure Gold Paths Table].DATA_GRP_ID)
WHERE ((([All Access Control Table].DATA_GRP_ID) Is Null));