Skip to main content

MySQL Query for getting nested items in a single query

 MySQL Query for getting nested items in a single query

 Here I would try to explain this with an example of categories , subcategories and maincategories which is handled via a nested structure in the table

How to get subcategory name , category name  and main category name in a single MySQL query

Suppose your category structure is like

MAIN CATEGORY >> CATEGORY  >> SUBCATEGORY

and You have a category table structure like this:

Table Name: tbl_category
CatId integer Autoincrement primary
CatName Varchar(25)
CatParent integer
CatSubParent integer
CatActive  integer


Suppose You have a category table with values like this

Understanding the nested table structure for storing categories , subcategories and maincategories in a single table

catId , catName , catParent , CatSubParent
1 , Books , 0 , 0
2 , DVD , 0 , 0
3 , Science , 1 , 0
4 , Maths , 1 , 0
5 , Movies , 2 , 0
6 , Music , 2 , 0
7 , English Movies , 2 , 5
8 , Malayalam Movies , 2 , 5
9 , Algebra , 1 , 4
10 , Physical Science , 1 , 3

Based on above table structure main category, category and subcategory values are identified by the presence of values in "catParent" and "CatSubParent" fields.

if catParent is 0 and CatSubParent is 0, then it is a Main category
if catParent is greater than 0 and CatSubParent is 0, then it is a  category
and if both catParent and CatSubParent field values are greater than 0 , then it is a sub category

So we are here is know how we can get subcategory name , category name and main 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, m.catName AS maincat
FROM tbl_category sc
LEFT JOIN tbl_category m ON m.catId = sc.catParent
LEFT JOIN tbl_category c ON c.catId = sc.catParentSub
WHERE (
sc.catParent IS NOT NULL
AND sc.catParentSub IS NOT NULL
AND sc.catParent <> ''
AND sc.catParentSub <> ''
)



this query will generate result like this

catid | subcat | cat | maincat
------------------------------
10 | Physical Science | Science | Books
9 | Algebra | Maths | Books
7 | English Movies |  Movies | DVD
8 | Malayalam Movies |  Movies | DVD

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



SELECT sc.catId AS catid, sc.catName AS subcat, c.catName AS cat, m.catName AS maincat
FROM tbl_category sc
LEFT JOIN tbl_category m ON m.catId = sc.catParent
LEFT JOIN tbl_category c ON c.catId = sc.catParentSub
WHERE (
sc.catParent IS NOT NULL
AND sc.catParentSub IS NOT NULL
AND sc.catParent <> ''
AND sc.catParentSub <> ''
)
ORDER BY m.catName, c.catName, sc.catName





Hope this would help someone :)

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

Popular posts from this blog

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

Your SBI Debit card ending with XX0000 is deactivated only for Internet txn.

SBI account holders may have received an SMS with following message, supposed to be from State Bank of India (SBI).

Your SBI Debit card ending with XX0000  is deactivated only for Internet txn. To activate send SMS "SWON ECOM 0000" to 09223966666. No change for ATM/POS usage
** Replace the four Zeros with last 4 digits of your debit card number

Recently many of the SBI account holder has losed their money due to a hi-tech ATM robbery which happened in Thiruvananthapuram, capital city of Kerala.

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