By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

NOT IN Subquery returns no records

P: 7
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];
Jun 1 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
I'm not sure what the size or data type of this desciption field is but you could try the following:
Expand|Select|Wrap|Line Numbers
  1.  
  2. 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]
  3. FROM Counterparty LEFT JOIN adjustments
  4. ON (counterparty.[Counter Party Description] = adjustments.[Counter Party Description] 
  5. And Counterparty.[Desk Name] = adjustments.[Desk Name])
  6. WHERE Counterparty.[Desk Name]="COMMODITIES" 
  7. AND Counterparty.[Cash Collateralized CE Short]<>0 
  8. AND adjustments.[Counter Party Description] Is Null
  9. ORDER BY Counterparty.[Cash Collateralized CE Short];
  10.  
Jun 2 '07 #2

P: 7
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.
Jun 4 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jun 4 '07 #4

P: 7
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.
Jun 4 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.