CODEWITHSUNDEEP
mysql
Karthik
Karthik

Reputation: 3301

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

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

Upvotes: 1

Views: 1317

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 158005

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

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 839234

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.

Upvotes: 1

Related Questions