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

Multiple Record Matching

P: 84
I have two tables that contain identical fields. (Account, Amount, Customer ID). I have a query that joins on those three fields. For the most part when matching records I get a one for one match. However, there are incidents where let say in table one I have 3 records that contain they following values (Account= 1234, Amount = 100.00, Customer ID = 9870. In table two I have 4 records that have Account = 1234, Amount = 100.00, Customer ID = 9870.) When I run the query I get 12 responses for this customer ID. When this condition exists, I only want the system to return the total of the lowest number (ie in the case above I expect to receive 3 matches since table 1 only has 3 records). Is there a way to limit the results in this type of query?
Feb 3 '09 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
So ...

Could you clarify a rule according to which certain combinations are defined to be within the "limit"?

Kind regards,
Fish
Feb 3 '09 #2

P: 84
I would think the rule would be...for each record in table 1, create only one match in table 2 where the Account, Amount and Customer ID match
Feb 3 '09 #3

Expert 100+
P: 1,287
This should work, though there may be a more efficient query possible:
SELECT * FROM Table1 WHERE EXISTS (SELECT Table2.Account FROM Table2 WHERE Table1.Account = Table2.Account AND Table1.Amount = Table2.Amount AND Table1.[Customer ID] = Table2.[Customer ID])
Feb 3 '09 #4

P: 84
That solution worked great...THX!!!
Feb 3 '09 #5

Post your reply

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