Modified Preordered Tree Traversal Query

[Super Phil]

Hi, I'm trying to rework my tree traversal query as too do more of the DB work in MySQL and clean up the PHP code. I'm trying to avoid using subselects as I want to stay compatabile with MySQL3x.

This is what I have right now, using a temporary table:

CREATE TEMPORARY TABLE tmp_par_ids (
	left_id INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
	right_id INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL);
INSERT INTO tmp_par_ids SELECT `left_id`, `right_id` from data where `id` = 4;
SELECT SQL_CACHE
	`id` ,
	data.right_id,
	data.left_id,
	`title` ,
	`level`,
	REPEAT(' ', `level`-1) html_level_spaces
FROM
	data,tmp_par_ids
WHERE
	`level` <= 5 AND
	data.left_id >= tmp_par_ids.left_id AND
	data.right_id <= tmp_par_ids.right_id
ORDER BY
	`left_id`;
DROP TABLE tmp_par_ids;

I would like to know if its possible to accomplish this in one query by selecting the table twice, using aliases, and do a inner/left join on itself to get the parent right / left ids - I'm not entirely sure how possible this is.

Also, what are the performance benefits with each solution?

This is my table set up:

CREATE TABLE `data` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `title` varchar(200) NOT NULL default '',
  `left_id` int(10) unsigned NOT NULL default '0',
  `right_id` int(10) unsigned NOT NULL default '0',
  `level` mediumint(8) unsigned NOT NULL default '0',
  UNIQUE KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=12 ;

#
# Dumping data for table `data`
#

INSERT INTO `data` VALUES (1, 'Music', 1, 14, 1);
INSERT INTO `data` VALUES (2, 'Ludo', 2, 5, 2);
INSERT INTO `data` VALUES (3, 'self titled', 3, 4, 3);
INSERT INTO `data` VALUES (4, 'weezer', 6, 13, 2);
INSERT INTO `data` VALUES (5, 'pinkerton', 7, 12, 3);
INSERT INTO `data` VALUES (6, 'el scorcho lyrics', 8, 9, 4);
INSERT INTO `data` VALUES (7, 'pink triangle lyrics', 10, 11, 4);
INSERT INTO `data` VALUES (8, 'Foods', 15, 22, 1);
INSERT INTO `data` VALUES (9, 'Meat', 16, 21, 2);
INSERT INTO `data` VALUES (10, 'Steak', 17, 18, 3);
INSERT INTO `data` VALUES (11, 'ribs', 19, 20, 3);

Thanks,
Phil

[Super Phil]

Apparently, it is I need to benchmark real quick, but I'm very sure this one will be faster.

SELECT SQL_CACHE
	nodes.id,
	nodes.right_id, 
	nodes.left_id, 
	nodes.level, 
	nodes.title,
	REPEAT(' ', nodes.level-1) html_level_spaces
FROM
	data as nodes, data as parent
WHERE
	nodes.left_id >= parent.left_id AND
	nodes.right_id <= parent.right_id AND
	parent.id = 4 AND
	nodes.level < 5
ORDER BY
	nodes.left_id