Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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 Comments1 Solution1393 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?