mysql fetch result parent id and category id by single query?

Question

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.

I need the output as tree structure like this,

Result is :

Information Science
    MCA
    M Tech
    B Tech
    BioTech
    InfoTech
Others Science
    Chemical
    Indus
    Physics

Answer 1

Maybe this is what you want:

SELECT
    T1.category AS category,
    T2.category AS subcategory
FROM category T1
JOIN category T2
ON T1.id = T2.parent

category               subcategory
'Information Science'  'MCA'
'Information Science'  'M Tech'
'Information Science'  'B Tech'
'Information Science'  'BioTech'
'Information Science'  'InfoTech'
'Others Science'       'Chemical'
'Others Science'       'Indus'
'Others Science'       'Physics'

This assumes that you only have two levels in your hierarchy.

Answer 2

Mysql has a nice article for you. Though personally I'd go for the Materialized Path