List:General Discussion« Previous MessageNext Message »
From:THO Date:September 18 2006 4:02am
Subject:tree query
View as plain text  
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...
Thread
tree queryTHO18 Sep
  • Re: tree queryTHO22 Sep