StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>this might help get you started - just think about how you'd remove a node without orphaning children !!</p> <p><a href="http://i.imagehost.org/0934/product_hier.jpg" rel="nofollow noreferrer">http://i.imagehost.org/0934/product_hier.jpg</a></p> <pre><code>-- the table drop table if exists product; create table product ( prod_id smallint unsigned not null auto_increment primary key, name varchar(32) not null, parent_id smallint unsigned null )engine = innodb; -- some test data insert into product (name, parent_id) values ('Products',null), ('Systems & Bundles',1), ('Components',1), ('Processors',3), ('Motherboards',3), ('AMD',5), ('Intel',5), ('Intel LGA1366',7); -- the stored proc for returning a hierachy drop procedure if exists product_hier; delimiter # create procedure product_hier ( in p_prod_id smallint unsigned ) begin declare p_done tinyint unsigned default(0); declare p_depth smallint unsigned default(0); create temporary table hier( parent_id smallint unsigned, prod_id smallint unsigned, depth smallint unsigned default 0 )engine = memory; insert into hier values (p_prod_id, p_prod_id, p_depth); /* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */ create temporary table tmp engine=memory select * from hier; while p_done <> 1 do if exists( select 1 from product p inner join hier on p.parent_id = hier.prod_id and hier.depth = p_depth) then insert into hier select p.parent_id, p.prod_id, p_depth + 1 from product p inner join tmp on p.parent_id = tmp.prod_id and tmp.depth = p_depth; set p_depth = p_depth + 1; truncate table tmp; insert into tmp select * from hier where depth = p_depth; else set p_done = 1; end if; end while; select p.prod_id, p.name as prod_name, b.prod_id as parent_prod_id, b.name as parent_prod_name, hier.depth from hier inner join product p on hier.prod_id = p.prod_id inner join product b on hier.parent_id = b.prod_id order by hier.depth, hier.prod_id; drop temporary table if exists hier; drop temporary table if exists tmp; end # delimiter ; -- the table select * from product; -- call the stored proc passing in the root node you want to get a sub-tree for call product_hier(1); call product_hier(3); call product_hier(5); </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload