仅显示包含产品的类别 [英] show only categories that have products in them

问题描述

请原谅这个糟糕的标题，但我找不到用抽象术语表达我想要的东西的好方法.

excuse the bad title but I couldn't find a good way to express what I want in abstract terms.

反正我有3张桌子

tbl_product:

tbl_product:

PID | productname
1   | product 1
2   | product 2
3   | product 3
4   | product 4
..

tbl_categories, motherCategory 允许我嵌套类别:

tbl_categories, motherCategory allows me to nest categories:

CID | categoriename    | motherCategory
1   | electronics      | NULL
2   | clothing         | NULL
3   | Arduino          | 1
4   | Casings, extra's | 3
..

tbl_productInCategory PID 和 CID 分别是 tbl_product 和 tbl_categories 中 PID 和 CID 的外键.一个产品可以分配多个类别，因此 PID 可以在此表中多次出现.

tbl_productInCategory PID and CID are foreign keys to PID and CID in tbl_product and tbl_categories respectively. A product can have multiple categories assigned to it so PID can occur more than once in this table.

PID | CID
1   | 1
2   | 1
3   | 3
4   | 4

现在我有一个查询，如果我给出mothercategory，它会返回所有类别.我想要做的是只显示递归地包含产品的类别.

Now I have a query that returns all categories if I give the mothercategory. What I want to do is show ONLY the categories that have products in them recursively.

例如在上面的示例数据中，我显示了所有类别(motherCategory 为空)，我希望它只返回电子产品，因为没有产品类别 2，服装.

for instance on the example data above I show all categories(motherCategory is null), I want it to return only electronics since there are no products category 2, clothing.

但是我遇到的问题是我也希望它递归地工作.考虑这个 tbl_productInCategory:

However the problem I am having is that I also want this to work recursively. Consider this tbl_productInCategory:

PID | CID
1   | 2
2   | 2
3   | 2
4   | 4

现在它应该返回服装和电子产品，即使电子产品中没有产品，因为嵌套类别 arduino->Casings, extra's 中有产品.如果我用 MotherCategory、electronics 显示所有类别，它也应该返回 arduino.

Now it should return both clothing and electronics even though there are no products in electronics, because there are products in the nested category arduino->Casings, extra's. If I show all categories with motherCategory, electronics it should also return arduino.

我不知道如何做到这一点，感谢任何帮助或指示.

I can't figure out how to do this and any help or pointers are appreciated.

推荐答案

首先，您应该选择存在产品的所有类别.在接下来的步骤中，选择母类别.

First you should select all categories where products exist. On the next steps select mother categories.

WITH CTE AS
(
SELECT tbl_categories.*
FROM
tbl_categories
JOIN tbl_productInCategory on tbl_productInCategory.CID = tbl_categories.CID

UNION ALL 

SELECT tbl_categories.*
FROM tbl_categories 
JOIN CTE on tbl_categories.CID = CTE.motherCategory 
)
SELECT DISTINCT * FROM CTE

这篇关于仅显示包含产品的类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！