On Wed, 26 Sep 2007 13:38:48 +0200, Joćo Morais <jcsmorais@gmail.com>
wrote:
> Hi there,
>
> I have a table like the one below:
>
> CREATE TABLE IF NOT EXISTS menuitens (
> id int(11) NOT NULL auto_increment,
> idMenu int(11) NOT NULL default 0,
> idParent int(11) NOT NULL default 0,
I'd say idParent should be NULL instead of 0 for root elements (has all
sorts of advantages, one of which is deleting a whole subtree in one
statement by cascading deletes).
> PRIMARY KEY (id)
> ) TYPE=INNODB;
>
> Because I will allow parent itens, I have that idParent thats
> corresponds to an existing id of menuitens table, my dought is, how
> can I have a query that lists all the data in the correct
> order, in other words something similar to the example below:
>
> Example:
>
> Row1
> Row2
> Row2.1
> Row2.2
> Row2.3
> Row3
> Row3.1
> Row4
> Row5
> Row6
> Row6.1
It could very well be the most referenced page concerning hierarchical
data, anyway, this will explain a lot:
http://dev.mysql.com/tech-resources/...ical-data.html
See the example "Retrieving a Full Tree" for the Adjacency Model. Jost add
order by clause (I assume on idMenu?) like:
ORDER BY t1.idMenu,t2.idMenu,..etc...
--
Rik Wasmus