Home  »     »     »  MySQL Query for getting subcategory name and parent category name in a single row

MySQL Query for getting subcategory name and parent category name in a single row

Saturday, March 31, 2012

How to get subcategory name and parent category name  in a single row with out using multiple query

Suppose You have a category table with values like this

catId,catName,catParent
1 , Books , NULL
2 , DVD , NULL
3 , Science , 1
4 , Maths , 1
5 , English Movies , 2
6 , Malayalam Movies , 2


Based on above table structure category and subcategory values are identified by the presense of values in catParent field.

if catParent is NULL, the it is a category and if have a numeric value then the record will be that of a subcategory.

So we are here is know how we can get subcategory name and parent category name  in a single row with out using multiple query

Try this query


SELECT sc.catId AS catid, sc.catName AS subcat, c.catName AS cat
FROM tbl_category sc
LEFT JOIN tbl_category c ON c.catId = sc.catParent
WHERE c.catParent IS NOT NULL



this query will generate result like this

catid | subcat | cat
-----------------------
3 | Science | Books
4 | Maths | Books
5 | English Movies | DVD
6 | Malayalam Movies | DVD


if you want to order the query based on Category name and then subcategory name, then try the below qry:

SELECT sc.catId AS catid, sc.catName AS subcat, c.catName AS cat
FROM tbl_category sc
LEFT JOIN tbl_category c ON c.catId = sc.catParent
WHERE c.catParent IS NOT NULL
ORDER BY c.catName, sc.catName





Hope this helps :)

Related article:
MySQL Query for getting nested items in a single query
How to get subcategory name , category name  and main category name in a single MySQL query




How to link to this page?
If you wish to link to this page from your website, simply Copy and paste the above HTML code to your web page. It will appear on your page as:
MySQL Query for getting subcategory name and parent category name in a single row.




Share this!




comments powered by Disqus

This Weeks 7 Popular Posts


Subscribe to Recent Posts by Email
Stay connected to CROZOOM with regular Email notices of new Techie articles and IT Jobs. Updates will be delivered to your Inbox as soon as they are posted online.

Enter Your Email Address:  

Delivered by FeedBurner   RSS Feed

Search this Blog   



Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »