Re: Recursive select to produce hierarchical listing
Hi. Maybe I am missing something, but won't SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHEREID=0 ORDER BY M1.Index, M2.Index work? Bye, Benjamin. On Tue, Nov 20, 2001 at 06:04:11PM -0800, [EMAIL PROTECTED] wrote: I am trying to display a hierarchically related list of folders via a recursive SQL statement (each row in a table represents a folder). What is needed is a select that will sort the folders and associate the parent folder with their child, sub-folders. The kicker is that the items in the folder need to be maintained in a specific order. An exanple: A] Mailbox 1) Folder A a - A sent b - A store c - A hold [...] Anyone have insight on how this may be accomplished? Here is one attempt at it but no luck so far: SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHERE ID=0; Full table definition dump below: CREATE TABLE Menu ( ID smallint(6) NOT NULL auto_increment, Index tinyint(4) NOT NULL default '0', ParentID smallint(6) NOT NULL default '0', FolderName varchar(32) default NULL, Level smallint(6) NOT NULL default '1', PRIMARY KEY (ID) ); Index is the order in which an item appears within a folder. Level is the level at which the folder is located, this makes it easier to indent the items in a folder. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Recursive select to produce hierarchical listing
Not sure if you're using PHP to manipulate MySQL or not, but if so, I had the same problem and found the answer from someone on PHPBuilder, and then I mirrored the answer on my site here: http://php.sitecreative.com/faq.php#3 Hope it helps. - Jonathan -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 2:43 PM To: Michael Collins Cc: [EMAIL PROTECTED] Subject: Re: Recursive select to produce hierarchical listing Hi. Maybe I am missing something, but won't SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHEREID=0 ORDER BY M1.Index, M2.Index work? Bye, Benjamin. On Tue, Nov 20, 2001 at 06:04:11PM -0800, [EMAIL PROTECTED] wrote: I am trying to display a hierarchically related list of folders via a recursive SQL statement (each row in a table represents a folder). What is needed is a select that will sort the folders and associate the parent folder with their child, sub-folders. The kicker is that the items in the folder need to be maintained in a specific order. An exanple: A] Mailbox 1) Folder A a - A sent b - A store c - A hold [...] Anyone have insight on how this may be accomplished? Here is one attempt at it but no luck so far: SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHERE ID=0; Full table definition dump below: CREATE TABLE Menu ( ID smallint(6) NOT NULL auto_increment, Index tinyint(4) NOT NULL default '0', ParentID smallint(6) NOT NULL default '0', FolderName varchar(32) default NULL, Level smallint(6) NOT NULL default '1', PRIMARY KEY (ID) ); Index is the order in which an item appears within a folder. Level is the level at which the folder is located, this makes it easier to indent the items in a folder. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Recursive select to produce hierarchical listing
I am trying to display a hierarchically related list of folders via a recursive SQL statement (each row in a table represents a folder). What is needed is a select that will sort the folders and associate the parent folder with their child, sub-folders. The kicker is that the items in the folder need to be maintained in a specific order. An exanple: A] Mailbox 1) Folder A a - A sent b - A store c - A hold 2) Folder B a - B sent b - B store c - B hold 3) Folder C a - C sent b - C store c - C hold B] File 1) Open 2) Close 3) Save C] Edit ... etc. ... Anyone have insight on how this may be accomplished? Here is one attempt at it but no luck so far: SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHERE ID=0; Full table definition dump below: CREATE TABLE Menu ( ID smallint(6) NOT NULL auto_increment, Index tinyint(4) NOT NULL default '0', ParentID smallint(6) NOT NULL default '0', FolderName varchar(32) default NULL, Level smallint(6) NOT NULL default '1', PRIMARY KEY (ID) ); Index is the order in which an item appears within a folder. Level is the level at which the folder is located, this makes it easier to indent the items in a folder. -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Recursive select to produce hierarchical listing
I am trying to display a hierarchically related list of folders via a recursive SQL statement (each row in a table represents a folder). What is needed is a select that will sort the folders and associate the parent folder with their child, sub-folders. The kicker is that the items in the folder need to be maintained in a specific order. An exanple: A] Mailbox 1) Folder A a - A sent b - A store c - A hold 2) Folder B a - B sent b - B store c - B hold 3) Folder C a - C sent b - C store c - C hold B] File 1) Open 2) Close 3) Save C] Edit ... etc. ... Anyone have insight on how this may be accomplished? Here is one attempt at it but no luck so far: SELECT M1.*,M2.* FROM Menu AS M1 JOIN Menu AS M2 ON M1.ID = M2.ParentID WHERE ID=0; Full table definition dump below: CREATE TABLE Menu ( ID smallint(6) NOT NULL auto_increment, Index tinyint(4) NOT NULL default '0', ParentID smallint(6) NOT NULL default '0', FolderName varchar(32) default NULL, Level smallint(6) NOT NULL default '1', PRIMARY KEY (ID) ); Index is the order in which an item appears within a folder. Level is the level at which the folder is located, this makes it easier to indent the items in a folder. -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
