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:
Code:
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:
Code:
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