使用 SQL 显示子类别 [英] display sub categories using SQL

问题描述

好吧，这有点令人困惑，所以我决定写一个新问题.

OK this is getting confusing so I decided to write a new question.

我需要检查类别表中的一行是否为子类别.然后我需要检查孩子属于哪个父类别?

I need to check if a row in my category table is a child category. I then need to check which parent category the child is part of?

父类别的 categoryID 和 ParentCategoryID 为 0一个子类别有一个 categoryID 和一个 ParentCategoryID，例如 30.

A parent category has a categoryID and a ParentCategoryID of 0 A child category has a categoryID and a ParentCategoryID eg 30.

类别表:

ID PCID NAME
10  0   Computers
11  10  Software
12  10  Hardware

这是我试过的:

这将显示父类别(因为 PCID 0 是父类别):

This will display the parent categories(because PCID 0 is the parent):

SELECT        CategoryID, ParentCategoryID, Name, Published, Deleted, PictureID
FROM          Nop_Category
WHERE        (Deleted = 0) 
AND          (Published = 1) 
AND          (ParentCategoryID = 0)

推荐答案

Self join back to the table to find the actual parent of child.

Self join back to the table to find the actual parent of child.

SELECT        c1.CategoryID, c2.ParentCategoryID, c1.Name, c2.Name as ParentName, c1.Published, c1.Deleted, c1.PictureID
FROM          Nop_Category c1
JOIN          Nop_Category c2 on c1.ParentCategoryId = c2.CategoryId
WHERE        (c1.Deleted = 0)  
AND          (c1.Published = 1)  
AND          (c1.ParentCategoryID = 10)

这将返回计算机"类别的两个子项.这就是你要找的吗?

This would return both children of the category "Computers". Is that what you're looking for?

当然，您可以反过来显示特定父级或所有父级的所有子级:

Of course, you can turn this around to display all the children of a specific parent or from all parents:

SELECT c.*, p.* -- shortened, but you should pick specific columns

FROM Nop_Category p -- parent
JOIN Nop_Category c ON c.ParentCategoryId = p.CategoryId -- children

WHERE p.ParentCategoryId = 0 -- all top level parents


SELECT c.*, p.* -- shortened, but you should pick specific columns

FROM Nop_Category p -- parent
JOIN Nop_Category c ON c.ParentCategoryId = p.CategoryId -- children

WHERE p.CategoryId = 10 -- only the "Computers" category

或者，如果您只想要计算机"类别的子项，请将您的 ParentCategoryId 更改为 10

Or, if you just want the children of category "Computers", change your ParentCategoryId to 10

SELECT        CategoryID, ParentCategoryID, Name, Published, Deleted, PictureID  
FROM          Nop_Category  
WHERE        (Deleted = 0)   
AND          (Published = 1)   
AND          (ParentCategoryID = 10)

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