Problem with recursive query
Hi,
I do have a problem that needs to solved recursively. As far as I know it's possible to call procedures recuresively (setting the max_sp_recursion_depth in cfg) but no functions. Am I right?
Now I have to solve a problem where invoices are subtracted from others which can have other "sub invoices" to be substracted, and so on. Ok, here is how my DB suctruce looks like:
CREATE TABLE `invoice` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`amount` decimal(8,0) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM;
CREATE TABLE `subtraction` (
`invoice_id` int(11) NOT NULL,
`subtract_id` int(11) NOT NULL
) ENGINE=MyISAM;
And here is example data:
INSERT INTO `invoice` VALUES (1,200),(2,500),(3,1200),(4,3400);
INSERT INTO `subtraction` VALUES (2,1),(3,2),(3,1),(4,3),(4,2),(4,1);
In real the amount of the invoice is an other table which will be summed up, but thats no problem. In this example I have four invoices. The First (id=1) has no sub-invoice to subctract, so the total amount is just the amount, which is 200. The second invoice has amount 500 minus 200, the first invoice, which is 300. Now it's getting tricky. From the third invoice (id=3) the total amount of invoice 1 and 2 has to be substracted:
1200 - ( 500 - 200 ) - 200 = 700
The same with invoice 4, where 3,2 and 1 has to be substracted recusively:
3400 - ( 1200 - ( 500 - 200 ) - 200 ) - ( 500 - 200 ) - 200 = 2200
I hope you understand my problem and what's the idea. I can limit the maximum recursion depth to 12 (or a little more) which stands for 12 month of a year to prevent infinite loops. And it would be quite easy if I could use functions for the calculation.
So does anyone ever had to solve a similar problem or has an idea?
thanks,
max
Edited 1 time(s). Last edit at 01/25/2013 12:35PM by Markus Manninger.