NOT IN Subquery returns no records

[rishhh]

The following query results no records. I am trying to exlude the Counter party Descriptions present in the Adjustments table, from the results of the main query. Any help is appreciated.

SELECT TOP 10 Counterparty.[Counter Party Description], Counterparty.[Entity Short Name] AS Entity, Counterparty.[Current Exposure Short] AS [Current Exposure], Counterparty.[Applied Nettable Cash Collateral Short] AS [Applied Nettable Cash Collateral], Counterparty.[Cash Collateralized CE Short] AS [Cash Collateralized CE], Counterparty.[NonNettable Cash and Security Collateral Short] AS [NonNettable Cash and Security Collateral]
FROM Counterparty
WHERE Counterparty.[Desk Name]="COMMODITIES"
AND Counterparty.[Cash Collateralized CE Short]<>0
AND counterparty.[Counter Party Description] NOT IN (SELECT DISTINCT adjustments.[Counter Party Description] FROM adjustments WHERE adjustments.[Desk Name] = "COMMODITIES")
ORDER BY Counterparty.[Cash Collateralized CE Short];

[MMcCarthy]

I'm not sure what the size or data type of this desciption field is but you could try the following:

 
SELECT TOP 10 Counterparty.[Counter Party Description], Counterparty.[Entity Short Name] AS Entity, Counterparty.[Current Exposure Short] AS [Current Exposure], Counterparty.[Applied Nettable Cash Collateral Short] AS [Applied Nettable Cash Collateral], Counterparty.[Cash Collateralized CE Short] AS [Cash Collateralized CE], Counterparty.[NonNettable Cash and Security Collateral Short] AS [NonNettable Cash and Security Collateral]
FROM Counterparty LEFT JOIN adjustments
ON (counterparty.[Counter Party Description] = adjustments.[Counter Party Description] 
And Counterparty.[Desk Name] = adjustments.[Desk Name])
WHERE Counterparty.[Desk Name]="COMMODITIES" 
AND Counterparty.[Cash Collateralized CE Short]<>0 
AND adjustments.[Counter Party Description] Is Null
ORDER BY Counterparty.[Cash Collateralized CE Short];
 

[rishhh]

Thanks a lot for your help. I highly appreciate it.

However, I still have one more problem. The 'adjustments' table may not have all the desk names, but will have only those desk names, which have some adjustments. So I can't use this query for desk names with no entry in adjustments table, as it returns nothing.

In each of the reporting cycle, the desks having adjustment would be different. So i am trying to find a generic criteria which will work in any case.

The # of records is small (about 70K records in 15 cols). Unfortunaltely, there are several limitations due to which i cannot use the normal database design techniques. i am using access as a temp storage space where i sort/aggregate the data and link it back to excel. The data is such that I cannot define primary keys on tables. But most of my queries are on single tables, do not involve joins, except for the one under consideration.

Thanks a million. I highly appreciate it.

[MMcCarthy]

Thanks a lot for your help. I highly appreciate it.

However, I still have one more problem. The 'adjustments' table may not have all the desk names, but will have only those desk names, which have some adjustments. So I can't use this query for desk names with no entry in adjustments table, as it returns nothing.

As I've used a LEFT JOIN this should return all records from the adjustments table. The following line excludes matching records

AND adjustments.[Counter Party Description] IS NULL

Remove it and see what results you get.

[rishhh]

As I've used a LEFT JOIN this should return all records from the adjustments table. The following line excludes matching records

AND adjustments.[Counter Party Description] IS NULL

Remove it and see what results you get.

Thanks a lot. It works!! I highly appreciate your help.