list all products of main category and subcategories

Question

I have 2 tables:

categories

id - name - parent
1 - Category 1 - 0
2 - Category 2 - 0
3 - Category 3 - 0
4 - Category 4 - 0
5 - Subcategory 11 - 1
6 - Subcategory 111 - 5
7 - Subcategory 112 - 5
...

products

id - name - category - description
1 - Name - 5 - Description

In categories table I have 3 levels

Category 1
- Subcategory 11
- Subcategory 111
- Subcategory 112
- Subcategory 113
Subcategory 12
- Subcategory 121
- Subcategory 122
- Subcategory 123
- Subcategory 13
Category 2
Category 3

I select products from Subcategories with:

mysql_query("SELECT *, products.name AS p_name, categories.name AS c_name
FROM products
INNER JOIN categories ON categories.id = products.category
WHERE categories.id = '". $_GET['items'] ."'
GROUP BY products.id DESC");

Products are in third level of categories(Subcategory 111, Subcategory 112, Subcategory 113, Subcategory 121...)

How to view all products from this categories also when I access parent category or main category(Category 1, Category 2, Subcategory 11, Subcategory 12...) ?

Thanks!

Err, so what is your question? What code are you having a problem with? — Captain Payalytic
How is the category hierarchy managed: a self-join in the categories table, an edges table? — Thomas
Captain, sorry! I need to view the products from subcategories when I acces main Category... — zmeutz

Thomas Thomas · Accepted Answer · 2013-05-24T15:42:54

If the number of levels of hierarchy are known or assumed, you can do something like:

Select Root.name
    , Level1.name As Level1Category
    , Level2.name As Level2Category
From Category As Root
    Left Join Category As Level1
        On Level1.parent = Root.id
    Left Join Category As Level2
        On Level2.parent = Level1.id
Where Root.parent = 0
Order By Root.name, Level1.name, Level2.name

To then view the products you would simply join on products to any level of the hierarchy:

Select Root.name
    , Level1.name As Level1Category
    , Level2.name As Level2Category
    , P.name As ProductName
From Category As Root
    Left Join Category As Level1
        On Level1.parent = Root.id
    Left Join Category As Level2
        On Level2.parent = Level1.id
    Join Products As P
        On P.category = Root.id
            Or P.category = Level1.id
            Or P.category = Level2.id
Where Root.parent = 0
Order By Root.name, Level1.name, Level2.name, P.name

list all products of main category and subcategories

1 Answers