CFLOOP Problem

[YogeshM]

Hi! I'd be grateful if someone could please help me solve this problem I'm
having. I have a query (e.g. qOne) that generates a quantity (e.g. 29) and I
have another query (e.g. qTwo) that generates a list of quantities (e.g. 1, 10,
16, 17, 19, 20) <cfquery name='qTwo' datasource='myDSN'> select
id, quantity from tableName where conditions </cfquery>
qTwo Output id quantity 1 1 2 10 3
16 4 17 5 19 6 20
How can I loop through qTwo so that I can obtain #qOne.quantity# i.e. 29, from
a combination of the quantities generated by qTwo, and output the all
corresponding id's from qTwo? E.g. 29 can be obtained by adding 10 and 19.
10 corresponds to id 2 and 19 correspnds to id 5. In other words how can I
obtain id 2 and 5 programmatically? Thanks and regards Yogesh Mahadnac

[jdeline]

Can more than two qTwo quantities be summed to produce qOne? In other words, if qTwo was 1, 2, 10, 16, 17, 19, 20, 26 then 10+19=29 and 1+2+26=29.

[YogeshM]

Hi!

Yes of course! There could have been more than 2 additions, as long as the
program does the right combinations to match #qOne.quantity#

Any idea how to do this please?

Regards,

Yogesh Mahadnac

[Kronin555]

I'm not sure how to do this in CF, but here's a quick-and-dirty java example of
the algorithm. The easiest way to accomplish this, at least the way I think, is
through recursion. There's definitely a way to do this without recursion, but
it'll be nasty (there are too many possibilities).



public class Recurse {
public void doSum(int desiredTotal, int[] ids, int[] values, int level,
String idList) {
if( desiredTotal > 0 ) {
for(int i = level; i < ids.length; i++) {
// recurse
doSum(desiredTotal - values[i], ids, values, i + 1, idList + ids[i] + ",")
;
}
} else if (desiredTotal == 0) {
System.out.println("Success! "+idList);
}
}

public static void main(String args[]) {
int desiredTotal = 29;
int[] ids = {1,2,3,4,5,6,7,8};
int[] values = {1,3,5,10,16,17,19,20};

Recurse recurse = new Recurse();
recurse.doSum(desiredTotal,ids,values, 0, "");
}
}

The output when this is run is as follows:
Success! 1,2,3,8,
Success! 2,4,5,
Success! 4,7,