Skip to main content

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

Popular posts from this blog

Payback Points - How to redeem - How to merge multiple payback accounts - Block Payback card - Payback customer care

How to apply for a new ration card and what are the documents required?

List of documents required for new ration card application in Kerala and how to apply


Application for a new ration card should be addressed to Taluk Supply Officer (TSO) / City Rationing Officer (CRO) of applicant's residing area.

Primary document required are Residence certificate, Income certificate and incase the applicant's name is in another ration card then he/she should submit the reduction certificate ontained from previous TSO / CRO as proof for removing his/her name from old Ration card.

You can get the new application from your currently residing Taluk Supply Office.

Joomla and Forum Integration - Integrating Forums to Joomla

Joomla is one of the most popular CMS opensource packages. It is very easier to develop website's using Joomla. You just need to download Joomla package from Joomla's Official website www.joomla.org  and install it on your domain and later adding customizations to templates and feature and Your website is ready :). Now a days most websites provides a forum section for it users for discussing various article topics, gathering opinions etc.



Following are some best know forum opensource packages which can be integrated with Joomla and create a new forum experience for users


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 »
Member
Search This Blog