Traversing a Category tree, is there an easy way to do this? Need an experts opinion!!!!!

[Simon .]

I have two pretty simple tables,

<----- Category ----->
DROP TABLE IF EXISTS `os_product`.`category`;
CREATE TABLE `category`
(
`category_id` int(10) unsigned zerofill NOT NULL auto_increment,
`name` varchar(30) NOT NULL,
`parent_category_id` int(10) unsigned zerofill NOT NULL default 0,
PRIMARY KEY (`category_id`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1;

AND

<------ Product ------>
DROP TABLE IF EXISTS `os_product`.`product`;
CREATE TABLE `product`
(
`product_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(30) NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`price` decimal(8,2) NOT NULL default '0.00',
`stock` int(6) unsigned NOT NULL default '0',
`allocated_stock` int(6) unsigned NOT NULL default '0',
`weight` decimal(4,3) NOT NULL default '0',
`manufacturer_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`product_id`),
KEY `manufacturer_id` (`manufacturer_id`),
FULLTEXT allsearch(`name`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1;

(ignore the manufacturer id as its irrelevant for this question)

Heres some sample data

<!----- Category ----->

DELETE FROM category;
INSERT INTO category
VALUES
('1', 'Antiques &amp; Art', '0'),
('2', 'Automotive', '0'),
. ('80', 'Cars', '2'),
. . ('85', 'BMW', '80'),
. . ('86', 'Ferarri', '80'),
. . ('87', 'Ford', '80'),
. . ('88', 'Peugeot', '80'),
. . ('89', 'Renault', '80'),
. ('81', 'Classic Cars', '2'),
. ('82', 'Motorcycles', '2'),
. ('83', 'Parts &amp; Accessories', '2'),
. ('84', 'Other Vehicles', '2'),
('3', 'Baby', '0'),
('4', 'Books, Comics &amp; Magazines', '0'),
('5', 'Business, Office &amp; Industrial', '0');

(ignore the '.'s just to make it easier to read)

now what i want to be able to do is from a parent category find all the subcategories and all the sub-subcategories all the way down to the end of the tree.

Obviously the length of the tree is not fixed.

this is being used though java-jdbc so i could write a number of cpu intensive recursive algorithms to execute many sql statements to traverse the tree, but obviously this would be quite time consuming, not to mention resource intensive.

Is there a simpler way?