troubleshooting Question

Adding different values from a table to find a specific sum value

Avatar of Cluskitt
CluskittFlag for Portugal asked on
Microsoft SQL ServerProgrammingCMicrosoft SQL Server 2005SQL
250 Comments3 Solutions1408 ViewsLast Modified:
There are times when I need to find a certain value out of a number of records. For example, assume this table:
tblValues (ID_Value int PK,ID_Code PK,CalcValue real)

It can have values like:
1,1,123.52
1,2,15421.23
2,1,2367
3,3,4352.12

Usually, it will have a few thousand rows (the actual table has more fields, but they're not relevant).
Sometimes, I need to find rows that, when added, will generate a certain number. For example, using the above values, let's say I need to find a value of 2490.52. If I add the 1st and 3rd rows, I will get that value. If I need to find a value of 6842.64, I will get it with the 1st, 3rd and 4th rows.

Usually, when such a need arises, we need to see if any one row has that value. That is basic SQL and I won't even bother with that. Next, we move to two values. I usually do it this way:
SELECT t1.ID_Value,t1.ID_Code,t1.CalcValue,
            t2.ID_Value,t2.ID_Code,t2.CalcValue
FROM tblValues t1
INNER JOIN tblValues t2
ON t1.ID_Value<>t2.ID_Value OR t1.ID_Code<>t2.ID_Code
WHERE t1.CalcValue+t2.CalcValue=2490.52
If we then need to move on to 3 rows, I add another table, and so on.

This approach has, basically, two problems:
1- Records get repeated. That is, it will return row 1 + row 3 AND row 3 + row 1. This quickly escalates as you add more tables
2- The query quickly gets too slow to be practical. We can only run a 4 rows sum on a small subset of the table and even then, it takes too long.

So, what is the best approach to this problem? Is there a more efficient way?
Even better, is there some way to return something like all possible one row combinations, then all possible two row combinations, then all of 3, etc, up to a reasonable max of, let's say, for example, 5?
Also important, is there any way not to get what are basically repeated records?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 250 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 3 Answers and 250 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