We help IT Professionals succeed at work.

display hierarchy in 1 column

thegroover
thegroover asked
on
892 Views
Last Modified: 2008-02-01
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);

Open in new window

Comment
Watch Question

Author

Commented:
i wrote in the subject that i need the result in 1 column, does not matter if 1 or 2, but if it is 1, i'd need the "catcode" column
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.