[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1125
  • Last Modified:

Sql query in tree hierarchy

Hi ,

We have 3 tables:
1 Categories (with column CategoryID, ParentID)
2. Products (with ProductID, other products related fields)
3. Category_product (with CategoryID and ProductID)

Category table have mult level  hierarchy i.e Category, Sub category, Sub Sub Category.

Now I want single query to display the products with the help of  Main CategoryID

For Example
There is one main category  with CategoryID =2
There are 3 sub category with IDs 6,7,8
Further each sub category have its own sub sub categories.

Now I want to display all products of categoryId 2 including its sub and sub sub categories.

Please suggest.
1 Solution
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
MySQL itself doesn't support hierarchical queries so the only way to do it in one query is to know exactly how deep you want to go into the subcategories.  Then you self join the categories the number of levels you want to go.

Say if you want to go 2 subcategories deep:

select p.*
from Categories a, Categories b, Categories c, Category_product cp, Products p
where a.CategoryID = 2
and a.CategoryID = b.ParentID
and b.CategoryID = c.ParentID
and (cp.CategoryID=a.CategoryID or cp.CategoryID=b.CategoryID or cp.CategoryID=c.CategoryID)
and p.ProductID=cp.ProductID


I don't use MS-SQL but you may be able to get an idea from Oreilly SQL Cookbook's recipe for the employee-manager relationship:

with x (tree,mgr,depth)
as (
select cast(ename as varchar(100)),
mgr, 0
from emp
where ename = 'MILLER'
union all
select cast(x.tree+'-->'+e.ename as varchar(100)),
e.mgr, x.depth+1
from emp e, x
where x.mgr = e.empno
select tree leaf___branch___root
from x
Please try the following (SQL Server 2008):

;With CategoryHierarchy(TopCategoryID, CategoryID, ParentID, CategoryLevel) As
    Select c.CategoryID TopCategoryID, c.CategoryID, c.ParentID, 0 As CategoryLevel
      From Categories c
     Where c.ParentID = 0
    Union All
    Select pc.TopCategoryID, sc.CategoryID, sc.ParentID, CategoryLevel + 1
      From Categories As sc
           Inner Join CategoryHierarchy As pc
                      On sc.ParentID = pc.CategoryID 

Select p.*
  From Products p
       Inner Join Category_Product cp
                  On cp.ProductID = p.ProductID
       Inner Join CategoryHierarchy ch
                  On ch.CategoryID = cp.CategoryID
 Where ch.TopCategoryID = 2

Open in new window

BTW, it assumes that the top category record has ParentID = 0.  If that's not the case, please update Line #5 accordingly.
GurbirsAuthor Commented:

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now