display sub categories using SQL

Question

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?

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

Category Table:

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

This is what Ive tried:

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)

Answer 1

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

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)