• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

Where NOT IN on composite-key

I'm hoping someone can help me with a little issue thats popped up. I've inherited a database I need to pull some data from. I am simplifying my situation for sake of making the post workable.

I have 2 tables, outlined below:

tblHoldings:
HoldingsId
AccountNumber
AssetId

tblReviews:
ReviewId
AssetId
AccountNumber

I need to find records in tblHoldings that do not have a match in tblReviews, but it needs to be on AssetId + AccountNumber

I've done WHERE NOT IN's before to do this quickly but I always have a single key (tblWhateverId) to use. However, here on tblHoldings it is a composite key so I'm not sure what to do.

For each [tblReviews.ReviewId], I need to look at the record's AssetId and AccountNumber and see if they don't exist for each [tblReviews.ReviewId].

The only thing I could figure to do was to check in the where

tblHoldings.AssetId + tblHoldings.AccountNumber NOT IN (select r.AssetId+r. AccountNumber from tblReviews r)

However, I know there is a proper, more efficient way to do this but its been so long since I've had to do something like this I can't remember. Can anyone show me a more proper, conventional way to do this?

Thanks in advance.
TRF
0
TheRoyalFalcon
Asked:
TheRoyalFalcon
  • 4
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT * FROM tblHoldings t
WHERE NOT EXISTS (SELECT 1 FROM tblReviews WHERE asserID = t.assetID and AccoutNumber = t.AccountNumber)
0
 
TheRoyalFalconAuthor Commented:
Aneeshattingal-

I don't know what happened. When I posted the question I'm sure I selected 250. When I rolled out it went 500. I was trying to update it, but it looks like you responded before i could get it updated. Do you mind if I ask a moderator to reset the points to where I planed (250)?

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
sure, no probs....
Our primary goal is to help you.. Points comes second .. :)

Aneesh
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
TheRoyalFalconAuthor Commented:
I just tried your solution and it worked like a charm. Thank you, very much! This problem was driving me up the wall most of the day yesterday.

And BTW, thank you very much for being so understanding. I appreciate that immensely!

As soon as they adjust the points I will accept the solution. It worked perfectly. Again thank you.

TRF
0
 
TheRoyalFalconAuthor Commented:
Scott - WOW that was fast. Thanks for the help.
0
 
TheRoyalFalconAuthor Commented:
Aneeshattingal - Thanks again for the help with this. Have a nice day! TRF.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now