Mysql查询合并自引用连接

Question

假设我有四个表:

------------- features --------------
id: int
name: varchar
-------------------------------------

-------- feature_categories ---------
feature_id: int
category_id: int    
-------------------------------------

----------- categories --------------
id: int
name: varchar    
-------------------------------------

------ category_subcategories -------
category_id: int
sub_category_id: int    
-------------------------------------

类别有很多(子)类别，通过自引用加入子类别

一个特征有很多类别，其中一些将是子类别，通过加入 feature_categories

我需要的是发送一组特征 ID 和一个(主要)类别 ID，并返回所有子类别。事实证明，这比我希望的要困难，所以我将不胜感激任何帮助。如果这个问题不清楚，请告诉我。

编辑我不需要将特征表包含在任何查询中。在facet中，我只要求返回subcategory(类别)名称字段。

Answer 1

必须稍微解决这个问题，但下面的任何一个都应该有效。第二个可能更有效率:

 select f.name featureName, 
  c.name CategoryName, 
  c2.name SubCategoryName  
    FROM features f, feature_categories fc, categories c, category_subcategories sc, categories c2, feature_categories fc2
    WHERE f.id = fc.feature_id
    AND c.id = fc.category_id
    AND sc.category_id = c.id
    and c2.id = sc.sub_category_id
    and fc2.category_id = c2.id
    AND f.id in (0,1,2,...)
    and fc2.feature_id in (0,1,2,...)
    AND c.id = @main_category_id

或:

    select f.name featureName, 
  c.name CategoryName, 
  c2.name SubCategoryName  
        FROM features f 
        inner join feature_categories fc on f.id = fc.feature_id
        inner join categories c on c.id = fc.category_id
        inner join category_subcategories sc on sc.category_id = c.id
        inner join categories c2 on c2.id = sc.sub_category_id
        inner join feature_categories fc2 on fc.category_id = c2.id

        WHERE f.id in (0,1,2,...)
        AND c.id = @main_category_id

        and fc2.feature_id in (0,1,2,...)