Your query works. But it depends on the data. If I break it down into its parts I get the following results:
Code:
Select ProductID
From ProductSection
Where SectionID = 1
I've used SectionID=1 as a known value, but you are wanting to use a variable value. I get the following productid results for that query.
Code:
975
2970
971
973
974
976
977
972
If I then expand the query to add-in the next part, like this:
Code:
Select CategoryID
from ProductCategory
Where ProductID IN (
Select ProductID
From ProductSection
Where SectionID = 1
)
I get the following categoryid results:
Code:
412
412
412
412
412
412
412
I would then have to know what the ParentCategoryID for my CategoryID=412 is:
Code:
select parentcategoryid from Category where CategoryID=412
Which in my case is 396.
Therefore, based on your original query, the only query that would work for me is:
Code:
Select CategoryID, Name, ImageFilenameOverride
From Category
Where ParentCategoryID = 396
and CategoryID IN (
Select CategoryID
from ProductCategory
Where ProductID IN (
Select ProductID
From ProductSection
Where SectionID = 1
)
)
Which gives, for us:
Code:
412 Belt Barriers & Posts {blank}
This tells me, all products that live in section (department) 1 also exist in category 412 and has a parent category of 412.
It's all about the data.