mysql - 如何按顶级父类别获取所有项目？

Question

我有 2 张桌子：shop_products和shop_category。
shop_products： id、name、category_id
shop_category： id、name、parent_id、活动。活动可以是“真”或“假”。类别嵌套如 parent>subcat>subsubcat>...
如何获得所有顶级父类别活动为“true”的产品？
我尝试使用 JOIN。但它只需要最接近的类别，而不是树的顶级类别。

SELECT * FROM shop_products 
INNER JOIN shop_category as sc1 ON (shop_products.category_id=sc1.id)
INNER JOIN shop_category as sc2 ON (sc1.parent_id=sc2.id)
WHERE sc2.active=1

UPD： 这里是sqlfiddle

我期望什么都得不到。因为顶级类别“Cat1”有活动假

标签： mysql

Answer 1

使用下面的查询：

SELECT s.* FROM shop_products as s WHERE EXISTS (
  SELECT id FROM shop_category as sc WHERE sc.id = s.category_id AND EXISTS(
  SELECT id FROM shop_category as sc1 WHERE sc.id = sc1.id AND sc1.active = 1

  )
);