display hierarchy in 1 column

hi,
i am learning stored procedures in mysql but am stuck at getting hierarchy results.
my table is in the code...so I have 3 levels now: grandparent->parent->child
the columns are as folows:
catcode: id of the category
catname: name of the category
catcode0: parent id of the cat
catcode0 parent name of the cat
focsop: 0 if grandparent, 1 if parent or child

I would need a procedure to which I pass a variable and it comes with the result:
grandparent
parent
child (which is the passed variable)

so if i call the proc with variable 56, i should get:
2   | 'main2'
52 | 'main2sub1'
56 | 'main2sub1sub4'

any help would be appreciated.

thanks
G

CREATE TABLE `categories` (
  `catcode` int(11) NOT NULL,
  `catname` varchar(100) default NULL,
  `catcode0` int(11) default NULL,
  `catname0` varchar(100) default NULL,
  `FOCSOP` tinyint(4) default NULL
);
 
INSERT INTO `categories` VALUES (3, 'main1', 0, NULL, 1);
INSERT INTO `categories` VALUES (2, 'main2', 0, NULL, 1);
INSERT INTO `categories` VALUES (1, 'main3', -1, NULL, 1);
INSERT INTO `categories` VALUES (52, 'main2sub1', 2, 'main2',0);
INSERT INTO `categories` VALUES (53, 'main2sub1sub1', 52, 'main2sub1', 0);
INSERT INTO `categories` VALUES (54, 'main2sub1sub2', 52, 'main2sub1', 0);
INSERT INTO `categories` VALUES (55, 'main2sub1sub3', 52, 'main2sub1', 0);
INSERT INTO `categories` VALUES (56, 'main2sub1sub4', 52, 'main2sub1', 0);
INSERT INTO `categories` VALUES (72, 'main3sub1', 1, 'main3',0);
INSERT INTO `categories` VALUES (73, 'main3sub1sub1', 72, 'main3sub1', 0);
INSERT INTO `categories` VALUES (74, 'main3sub1sub2', 72, 'main3sub1', 0);
INSERT INTO `categories` VALUES (75, 'main3sub1sub3', 72, 'main3sub1', 0);
INSERT INTO `categories` VALUES (76, 'main3sub1sub4', 72, 'main3sub1', 0);

Select allOpen in new window