In need of suggestions to get correct part multiplier factor. (apologies up front for message length)
I have an parts/assemblies table. mysql> describe assycard; +----------+-----------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-----------------+------+-----+---------+-------+ | part_id | int(7) unsigned | NO | PRI | 0 | | | assy | int(7) | NO | PRI | 0 | | | qty_assy | int(7) unsigned | NO | | 0 | | | unit | char(10) | NO | | EA | | +----------+-----------------+------+-----+---------+-------+ This table forms an assembly tree and parts list resource. qty_assy is the qty of each part (part_id) that belongs to an assembly (assy). Assembly number assy can also be a part_id and in that case is called a sub-assembly and has a qty_assy value for how many sub-assemblies belong to its parent assembly. I have a procedure that eventually, properly generates the tree... Thanks to info from http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html What I have been unable to get right is the qty_assy multipliers up the tree. truncated example; +---------+--------------------------------+-------------+ | part_id | assy | qty_assy | +---------+--------------------------------+-------------+ | 8100028 | 8100030 | 8 | | 8100029 | 8100028 | 6 | | 1000100 | 8100029 | 1 | | 2100050 | 8100029 | 4 | +---------+--------------------------------+-------------+ parts 1000100 and 2100050 belong to assembly 8100029 which is a child of 8100028, 8100028 is a child of the top assembly 810030. In this example there are 6 8100029's in 8100028 and 8 8100028's in 8100030 therefore every part in 8100029 should be multiplied by 48 to account for all parts from 8100029 included in 1 8100030 (48 1000100's and 192 2100050's). Parts in 8100028 should only be multiplied by 8 to account for parts in 8100028 included in 8100030. The actual depth of the tree is in principal unknown so I need to walk the tree and correctly collect the multipliers for each assembly. The heart of the assembly tree procedure that works, with some cleaning up of the result, is below but carrying the qty factors along the way did not work... DROP PROCEDURE IF EXISTS ListDescendants; DELIMITER | CREATE PROCEDURE scanner.ListDescendants( IN ancestor int(7) ) BEGIN DECLARE rows INT DEFAULT 0; -- CREATE NEEDED TABLES DROP TABLE IF EXISTS descendants; CREATE TABLE IF NOT EXISTS descendants ( d_part_id int(7), d_assy int(7), d_qty int(5) ) ENGINE=MEMORY; DROP TABLE IF EXISTS nextparents; CREATE TABLE IF NOT EXISTS nextparents ( assy int(7), np_qty_assy int(5) ) ENGINE=MEMORY; DROP TABLE IF EXISTS prevparents; CREATE TABLE prevparents LIKE nextparents; -- SEED NEXTPARENTS INSERT INTO nextparents SELECT assy, qty_assy FROM assycard WHERE assy=ancestor and qty_assy!=0; SET rows = ROW_COUNT(); WHILE rows > 0 DO -- ADD CHILDREN OF NEXTPARENTS INSERT INTO descendants SELECT t.part_id, t.assy, t.qty_assy FROM assycard AS t INNER JOIN nextparents USING(assy) where t.part_id!=0; SET rows = ROW_COUNT(); -- SAVE COPY OF NEXTPARENTS TRUNCATE prevparents; INSERT INTO prevparents SELECT * FROM nextparents; -- NEXT PARENTS ARE CHILDREN OF THESE PARENTS: TRUNCATE nextparents; INSERT INTO nextparents SELECT part_id, qty_assy FROM assycard INNER JOIN prevparents USING (assy) where assycard.part_id!=0; SET rows = rows + ROW_COUNT(); END WHILE; ... cleanup of resulting descendants table, joining with part and cost information from other tables... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
