如何在产品和类别应用程序树中将1个表连接到（2个不同的表作为一个表）？
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
但是那个sql给了我这个结果
+-----------+-----------+-----------+
| 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:
我希望将产品表视为类别（级别2）表，因此如果像TV这样的父节点有一些子类别和子产品，我就得到了它们。如何获得这样的结果树：
+-------------+------------+------------+
| 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)
电视（类别）> LG（产品）
TV(category) > LCD(category) > HD(product)
电视（类别）> LCD（类别）> HD（产品）
TV(category) > LCD(category) > FHD(product)
电视（类别）> LCD（类别）> FHD（产品）
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)
2 个解决方案
#1
1
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:
看它在SQLFiddle上运行：
| lvl1 | lvl2 | lvl3 |
|------|--------|--------|
| TV | LCD | HD |
| TV | LCD | FHD |
| TV | LG | (null) |
| TV | PLASMA | (null) |