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:
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:
FROM tblValues t1
INNER JOIN tblValues t2
ON t1.ID_Value<>t2.ID_Value OR t1.ID_Code<>t2.ID_Code
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?