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
LVL 1
TheRoyalFalconAsked:
Who is Participating?
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.