It's been my experience that adjacency lists like what you describe are difficult to query. I have had success with nested sets to represent that kind of hierarchical data.
More information at: http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html -----Original Message----- From: Joe Mellon [mailto:[EMAIL PROTECTED] Sent: Thursday, February 12, 2004 4:38 AM To: [EMAIL PROTECTED] Subject: SELF JOIN implementing a logical tree on one table Hi Mysqlers, I have a table which implements a tree-like heirarchy: In the example below a geographical heirarchy. I would like to be able to pick a node in the tree and Get everything logically below that node in the heirarchy: (A self join?) # # Table structure for `tree` # CREATE TABLE `tree` ( `name` varchar(40) NOT NULL default '', `parentname` varchar(40) NOT NULL default '' ) TYPE=MyISAM; # # Data for Table `tree` # INSERT INTO `tree` VALUES ('USA', '""'); INSERT INTO `tree` VALUES ('CA', 'USA'); INSERT INTO `tree` VALUES ('AZ', 'USA'); INSERT INTO `tree` VALUES ('Orange', 'CA'); INSERT INTO `tree` VALUES ('Red', 'CA'); INSERT INTO `tree` VALUES ('Blue', 'CA'); INSERT INTO `tree` VALUES ('Apache', 'AZ'); INSERT INTO `tree` VALUES ('Navajo', 'AZ'); INSERT INTO `tree` VALUES ('Mohawk', 'AZ'); INSERT INTO `tree` VALUES ('Monument', 'Apache'); INSERT INTO `tree` VALUES ('Statue', 'Apache'); I want to get e.g. all the locations in AZ. SELECT t1.name, t1.parentname FROM `tree` AS t1 LEFT JOIN tree as t2 ON t1.parentname=t2.name WHERE t2.name='AZ'; Gets me Apache AZ Navajo AZ Mohawk AZ But not the nodes with names Monument and Statue SELECT t1.name, t1.parentname FROM `tree` AS t1 LEFT JOIN tree as t2 ON t1.parentname=t2.name LEFT JOIN tree as t3 ON t2.parentname=t3.name WHERE t3.name='AZ'; Gets me Monument Apache Statue Apache But not the nodes with names Apache, Navajo, Mohawk Can anyone tell me the correct syntax to pick a node in the tree and get everything logically below that node in the heirarchy? Thanks Joe Mellon, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
