TheRoyalFalcon
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sure, no probs....
Our primary goal is to help you.. Points comes second .. :)
Aneesh
Our primary goal is to help you.. Points comes second .. :)
Aneesh
ASKER
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
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
ASKER
Scott - WOW that was fast. Thanks for the help.
ASKER
Aneeshattingal - Thanks again for the help with this. Have a nice day! TRF.
ASKER
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)?