how to join 1 table to (2 different tables as one) in a tree of products and categories application?

Question

I'm making a tree of products and categories. the tree accepts the products to be exist in any category whether it is a parent node or a leaf.

here are my tables

#category table
+------+-----------+-------------+
|  ID  |  cat_name | parent_cat  |
+------+-----------+-------------+
|   1  |     TV    |     NULL    |
+------+-----------+-------------+
|   2  |    LCD    |      1      |
+------+-----------+-------------+
|   3  |   PLASMA  |      1      |
+------+-----------+-------------+


#product table
+------+-----------+-----------+
|  ID  |  prd_name |  prd_cat  |
+------+-----------+-----------+
|   1  |     LG    |     1     |
+------+-----------+-----------+
|   2  |     HD    |     2     |
+------+-----------+-----------+
|   3  |    FHD    |     2     |
+------+-----------+-----------+

what I want is to make a select query to retrieve the full tree containing all the categories and products.

I can retrieve the categories with this query

select cat1.cat_name lvl1, cat2.cat_name lvl2
from category cat1 left join category cat2 on cat1.ID = cat2.parent_cat
where cat1.parent_cat is null;

this gives me something like this

+------+------+
| lvl1 | lvl2 |
+------+------+
|  TV  |  LCD |
+------+------+
|  TV  |PLASMA|
+------+------+

when I tried to join the products in the tree I used this query

select cat1.cat_name,cat2.cat_name,product.prd_name
from category cat1 left join category cat2 on cat1.ID = cat2.parent_cat
left join product on cat1.ID = product.prd_cat
where cat1.parent_cat is null;

but that sql gives me this result

+-----------+-----------+-----------+
|  cat_name |  cat_name |  prd_name |
+-----------+-----------+-----------+
|    TV     |    LCD    |     LG    |
+-----------+-----------+-----------+
|    TV     |   PLASMA  |     LG    |
+-----------+-----------+-----------+

I want the product table to be treated as the category (level 2) table so if a parent node like TV have some child categories and child products, I got them both. How to get a result tree like this one:

+-------------+------------+------------+
| tree_lvl_1  | tree_lvl_2 | tree_lvl_3 |
+-------------+------------+------------+
|      TV     |     LG     |    NULL    |
+-------------+------------+------------+
|      TV     |     LCD    |     HD     |
+-------------+------------+------------+
|      TV     |     LCD    |     FHD    |
+-------------+------------+------------+
|      TV     |   PLASMA   |    NULL    |
+-------------+------------+------------+

TV(category) > LG(product)

TV(category) > LCD(category) > HD(product)

TV(category) > LCD(category) > FHD(product)

TV(category) > PLASMA(category)

edit: this question that you suggested is about building the tree of only categories, I know how to do that. my problem here is I have also "products" table that should join the tree as a node just like the category ! I want category and product tables be treated as one table(node)

How deep is your `category` table? Could it have level 3, level 4,...? — Pham X. Bach, Feb 26 '17 at 11:19
Possible duplicate of [Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)](http://stackoverflow.com/questions/5291054/generating-depth-based-tree-from-hierarchical-data-in-mysql-no-ctes) — mroach, Feb 26 '17 at 11:21
@PhamX.Bach , yes this products tree is 4 levels. and I will apply the same tree concept in an accounts tree which will be much deeper may be 7 levels. — Accountant م, Feb 26 '17 at 11:22
@Accountantم this question has been asked and answered on SO sooo many times. Searching for tree traversal or hierarchical data would give you the answer. — Shadow, Feb 26 '17 at 11:29
@mroach thanks for the link. my problem is I have another table (product) that should be treated as a node in the tree as well. — Accountant م, Feb 26 '17 at 11:32
@Shadow all the cases I found from searching is about the tree of one table "categories" containing all the nodes, my problem here is I have another table "products" that should join the tree as a node too — Accountant م, Feb 26 '17 at 11:49
The products are leaves only. You build your category tree and join your products as leaves to it. — Shadow, Feb 26 '17 at 12:16
@Shadow do you mean I build the category tree in 1 query, then join the leaves with another query in the programming code (php in my case) ? or I do that in the same select query ? — Accountant م, Feb 26 '17 at 13:49

score 2 · Accepted Answer · answered Feb 26 '17 at 13:13

You can solve this by first removing the complexity of having two tables. Create a view that combines category records with product records, like this:

create view combi as
  select 'P' as type, 
         id, 
         prd_name as name, 
         prd_cat as parent_id
  from   product
  union all
  select 'C' as type, 
         id,
         cat_name, 
         parent_cat
  from   category;

Now you can do the query based on this view, making sure to add the type requirement in the join condition:

select    lvl1.name as lvl1,
          lvl2.name as lvl2,
          lvl3.name as lvl3
from      combi as lvl1
left join combi as lvl2 on lvl1.id = lvl2.parent_id and lvl1.type = 'C'
left join combi as lvl3 on lvl2.id = lvl3.parent_id and lvl2.type = 'C'
where     lvl1.parent_id is null;

See it run on SQLFiddle:

| lvl1 |   lvl2 |   lvl3 |
|------|--------|--------|
|   TV |    LCD |     HD |
|   TV |    LCD |    FHD |
|   TV |     LG | (null) |
|   TV | PLASMA | (null) |

you are an angel. thaaaank you, that is exactly what I was trying to do — Accountant م, Feb 26 '17 at 13:42
Is my schema (product table and category table) good? or do you recommend change it ? perhaps 1 table for both (products and categories) with the same view fields (id,name,parent) and another separate table for other product fields (e.g. bar-code, selling price,..etc). sorry for bothering you. — Accountant م, Feb 26 '17 at 18:48
If indeed you are going to use this view frequently, then it might be useful to do as you suggest. On the other hand, it is not bad to keep it like it is either. — trincot, Feb 26 '17 at 19:46

score 0 · Answer 2 · answered Feb 26 '17 at 12:55

What you have here, Accountant, is not an unbalanced hierarchy (where the various branches of the hierarchy tree are of different lengths), but a ragged hierarchy.

A hierarchy is ragged when you have a fix lowest level (product, in your case) and an unbalanced hierarchy above it (category, in your case)

So, in theory:

cat_l1|cat_l2|prd
TV    |NULL  |LG
TV    |LCD   |HD
TV    |LCD   |FHD
TV    |PLASMA|NULL

My answer bases itself on de-facto standards of designing and populating dimension tables containing this type of hierarchy. The lowest level is the primary key level, so it's the product. And classic table design puts the primary key first. Then, I continue by the order of the expected cardinality in the columns - down the levels of the hierarchy.

Filling holes in ragged hierarchies is achieved by artificially balancing all levels but the lowest: you copy the parent levels down until all levels are filled.

You have no products for the PLASMA subcategory. With this design, either you don't create any rows for that subcategory, or you create a 'not applicable' product entry with, for example, a negative ID. Like so:

cat_l1|cat_l2|prd
TV    |TV    |LG
TV    |LCD   |HD
TV    |LCD   |FHD
TV    |PLASMA|not applicable

The script below suppressed the subcategories with no products. To change it to return the report above, you will have to LEFT OUTER JOIN the last subcategory level to the product, and use an IFNULL(prd_name,'not applicable') AS prd_name. Not exactly what you asked for, but following some de-facto standards ... The input data are included in the query; all you have to do is remove the WITH clause.

    WITH
    category(cat_id,cat_name,parent_cat) AS (
                        SELECT 1,'TV',NULL::INT
    UNION ALL SELECT 2,'LCD',1
    UNION ALL SELECT 3,'PLASMA',1
    )
    ,
    product(prd_id,prd_name,prd_cat) AS (
                        SELECT 1,'LG',1
    UNION ALL SELECT 2,'HD',2
    UNION ALL SELECT 3,'FHD',2
    )
    SELECT
        prd.prd_id       AS prd_id
    , cat_l1.cat_name  AS cat_l1_name
    , cat_l1.cat_name  AS cat_l2_name
    , prd.prd_name     AS prd_name
    FROM category cat_l1
    JOIN product  prd
        ON prd_cat = cat_l1.cat_id
    WHERE cat_l1.parent_cat IS NULL
    UNION ALL
    SELECT
        prd.prd_id       AS prd_id
    , cat_l2.cat_name  AS cat_l2_name
    , cat_l1.cat_name  AS cat_l1_name
    , prd.prd_name     AS prd_name
    FROM category cat_l1
    JOIN category cat_l2
        ON cat_l2.parent_cat = cat_l1.cat_id
    JOIN product  prd
        ON prd_cat = cat_l2.cat_id
    WHERE cat_l1.parent_cat IS NULL
    ;

prd_id|cat_l1_name|cat_l2_name|prd_name
     1|TV         |TV         |LG
     2|LCD        |TV         |HD
     3|LCD        |TV         |FHD

Happy playing ....

Marco the Sane

Mysql only supports CTEs in v8.0, which is not even in apha status, so your solution is not really useful for mysql. — Shadow, Feb 26 '17 at 13:14

how to join 1 table to (2 different tables as one) in a tree of products and categories application?

2 Answers2