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