query help -multple in's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query help -multple in's

Author

Topic

biggunn
Starting Member

4 Posts

Posted - 2011-07-27 : 14:38:01

Here's the scenario... 3 tables

Category
CategoryID, ParentCategoryID

ProductCategory
CategoryID,ProductID

ProductSection
SectionID, ProductID

I need all of the CategoryID's from Category with a specific ParentCategoryID, that has a categoryID in ProductCategory, that has a ProductID in ProductSection that has a particular SectionID or in other words..

Get ProductIDs from ProductSection with SectionID of x
Get CategoryIDs from ProductCategory with ProductIDs filtered from Product Section
Get CategoryIDs from Category filtered from ProductCategory where ParentCategoryID = y

Should really not be that complicated, but for some reason I just can't get it worked out and I am sure I am missing something obvious. Here is where I am at now.

Select CategoryID From Category Where ParentCategoryID = 1234 and CategoryID IN(Select CategoryID from ProductCategory Where ProductID IN(Select ProductID From ProductSection Where SectionID = 10))

Right now it is returning all of the CategoryID's with a ParentCategoryID of 1234 and not filtering based on SectionID at all. If anyone can see what I am obviously missing I would greatly appreciate it.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-08-02 : 01:09:19

Hey there, this isn't using an "in clause" but should get the results you're asking for:

Select A.CategoryId
From Category A
Join ProductCategory B ON B.CategoryId=A.CategoryId
Join ProductSection C ON C.ProductId=B.ProductId

Where A.ParentCategoryId=1234 and C.SectionId=10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources