Sql query in tree hierarchy

Posted on 2011-04-21
Last Modified: 2012-06-27
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.
Question by:Gurbirs
    LVL 19

    Expert Comment

    by:Rikin Shah
    LVL 24

    Accepted Solution

    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
    LVL 23

    Expert Comment

    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

    LVL 23

    Expert Comment

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

    Author Closing Comment


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now