SQL类别树 - 类，categoryrelationship和产品表 - 产品需求总量为每个SUBCAT

Question

我有3场我感兴趣的类别表：

ID, Name, CategoryType 

根节点由具有鉴定的1 CategoryType，根节点的子节点是2型和子 - 子节点是类型3

有一个第二表，CategoryRelationship，其中两列是重要的：

CategoryID, ParentCategoryID。

我想是一个记录的上市让我们有每个类别/子类别的名称和它的ID，如下面

ID RootName1 ID SubName1 ID Sub-SubName1 
ID RootName1 ID SubName1 ID Sub-SubName2 
ID RootName1 ID SubName2 ID Sub-SubName1 
ID RootName1 ID SubName2 ID Sub-SubName2 
ID RootName1 ID SubName2 ID Sub-SubName3 
ID RootName2 ID SubName1 ID Sub-SubName1 
ID RootName2 ID SubName2 
ID RootName2 ID SubName3 

的ID将每一根和节点/子节点等

我想我有这个工作 - 我只是想知道这是否是这样做的“正确”方式，或者如果这是一个更好的方法。这是针对MS SQL 2012 express数据库完成的。

select c.id, 
     c.name, 
     c1.Name, 
     cr1.CategoryID, 
     c2.Name, 
     cr2.CategoryID 
from Category c 
left outer join CategoryRelationship cr1 on cr1.CategoryParentID = c.id 
left outer join CategoryRelationship cr2 on cr2.CategoryParentID = cr1.CategoryID 
inner join Category c1 on c1.ID = cr1.CategoryID 
inner join Category c2 on c2.id = cr2.CategoryID 
where c.CategoryTypeID = 1 
order by c.name, c1.name, c2.name 

还有一点点，我需要一点帮助。有第三张桌上有产品。每个产品都有一个SubCateoryID，它可以匹配上面的cr2.CategoryID。我想为每个cr2类别显示产品表中的项目总数，并且我仍然希望在产品表格中包含任何没有项目的类别。我不知道如何做最后一部分。

Answer 1

我想我有这样的：

select c.ID, c.Name, c1.Name, cr1.CategoryID, c2.Name, cr2.CategoryID, 
(select count(*) from Product where product.SubcategoryID = cr2.CategoryID and Deleted = 0 and StatusID = 1) 
from Category c 
left outer join CategoryRelationship cr1 on cr1.CategoryParentID = c.id 
left outer join CategoryRelationship cr2 on cr2.CategoryParentID = cr1.CategoryID 
inner join Category c1 on c1.ID = cr1.CategoryID 
inner join Category c2 on c2.id = cr2.CategoryID 
where c.CategoryTypeID = 1 
order by c.name, c1.name, c2.name