I have the table and values like this,
CREATE TABLE `category` (
`id` INT( 4 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`category` VARCHAR( 50 ) NOT NULL ,
`parent` INT( 4 ) NOT NULL
)
INSERT INTO `category` VALUES (1, 'MCA', 9);
INSERT INTO `category` VALUES (2, 'M Tech', 9);
INSERT INTO `category` VALUES (3, 'B Tech', 9);
INSERT INTO `category` VALUES (4, 'BioTech', 9);
INSERT INTO `category` VALUES (5, 'InfoTech', 9);
INSERT INTO `category` VALUES (6, 'Chemical', 10);
INSERT INTO `category` VALUES (7, 'Indus', 10);
INSERT INTO `category` VALUES (8, 'Physics', 10);
INSERT INTO `category` VALUES (9, 'Information Science', 0);
INSERT INTO `category` VALUES (10, 'Others Science', 0);
I need a single query for getting the values in terms of category and subcategory. 3rd values zero as category and others are subcategory.
