troubleshooting Question

Where NOT IN on composite-key

Avatar of TheRoyalFalcon
TheRoyalFalcon asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
6 Comments1 Solution559 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Aneesh
Database Consultant
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros