Hi people

I wonder if anyone can help (I hope I have the right forum for this, apologies if I don´t).

I am scratching my head with this one....

I have a db table called transactions.
A transaction record: tran_id, number_of_points_given, date_given....

(The field number_of_points_given can hold a value from 1 to 10)


I also have prize table with records of prizes, e.g:
A prize record: pr_id, pr_desc, pr_num_points_reqd, ....

When a prize is claimed, I need to calculate the number of transaction records that need to be deleted. The value from the prize claimed (prize.number_of_points_given) should equal the sum of X number of transaction records´ number_of_points_given.

For example, a prize is worth 10 points. A customer has 100 transaction records, each with a value in their num_points_given of 1-10. The customer claims the prize so only X transaction records need to deleted where X is the sum of the number_of_points_given in the X transaction records AND this number_of_points_given total equals the prize.num_points_reqd.

So the number of transaction records to be deleted is determined by not only the number_of_points_reqd but the sum of the transaction records´ number_of_points_given fields.


If the transaction records were each just worth 1 point, I could limit the number of transactions selected by the prize.number_points_reqd.


Any tips would be great!