I need some help please I am creating SP, and I want to sum all the amount in the left of my parentid.
CREATE TABLE IF NOT EXISTS `mytree` (
`parentid` int(11) NOT NULL,
`memberid` int(11) NOT NULL,
`position` char(1) NOT NULL,
`amount` decimal(10,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Here is my sample data
INSERT INTO `mytree` (`parentid`, `memberid`, `position`, `amount`) VALUES
(8, 27, 'R', 0.00),
(8, 28, 'L', 0.00),
(24, 26, 'R', 0.00),
(0, 1, '', 5500.00),
(24, 25, 'L', 0.00),
(21, 24, 'L', 500.00),
(21, 23, 'R', 0.00),
(18, 20, 'R', 1500.00),
(18, 19, 'L', 0.00),
(15, 18, 'R', 2000.00),
(15, 17, 'L', 0.00),
(13, 16, 'L', 0.00),
(13, 15, 'R', 2500.00),
(12, 14, 'R', 0.00),
(12, 13, 'L', 3000.00),
(10, 12, 'R', 3500.00),
(10, 11, 'L', 0.00),
(7, 10, 'R', 4000.00),
(7, 9, 'L', 0.00),
(5, 8, 'R', 500.00),
(5, 7, 'L', 4500.00),
(1, 6, 'R', 0.00),
(1, 5, 'L', 5500.00),
(20, 22, 'R', 0.00),
(20, 21, 'L', 1000.00);
here is my SP
CREATE DEFINER=root@localhost PROCEDURE sum_amount(IN p_memid INT, OUT tot_left_amount DECIMAL(10,2))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ‘’
BEGIN
DECLARE a decimal(10,2);
DECLARE m int;
DECLARE s decimal(10,2);
select memberid, amount into m, a from mytree
where parentid = p_memid and position = 'L';
call sum_amount(m,s);
set tot_left_amount = a + s;
END
example if the parentid is 5 ,so the lefthchild of it is 7 sum all the subtrees on it, if I’m not wrong the total is 22,500 amount ?
Thank you in advance.