urgent-- please help

[557]

can someone please help me with this problem

i have a table with about 1000 records. each record has many detail fields and an amount field. when the user gives a particular amount ,say 10000, i want to find all combinations of records in this table which will give me a sum of exactly this amount 10000. there may be 1 record or 2 records or 3 records or n records that give me this amount as sum. how do i get all the combinations in this table for this requirement?

 

[r937]

you can only do this if you have something to GROUP BY, and that won't really be "all combinations"

otherwise you'll have to use application program logic

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[557]

thanks r937

 

[stevexff]

You could start by selecting all rows from the table where AMOUNT <= 10000, which will at least exclude any rows where the amount is too big to start with. Then you need to come up with an algorithm to find all the combinations (not permutations) that match.

Is this just an intellectual exercise, or do you have a business requirement to satisfy? It's just that this looks a bit like an NP-complete travelling salesman type problem, so don't expect it to be easy...

 

[557]

not an intellectual exercise. there is a requirement. i've come up with an algorithm. i'll get back if it doesn't work as expected