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

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