CODEWITHSUNDEEP
sqlsql-server-2005
Joe
Joe

Reputation: 2107

How do I get the child items of child items?

I have a database with four tables: ParentCategory, ChildCategory, ItemsCategories, and Items. The table named ItemsCategories is a join table since a a row in Item can listed multiple times in ChildCategory. The primary key in all tables is named ID. The foreign key column in the ChildCategory is ParentCategoryID and the foreign keys in ItemsCategories are ItemID and ChildCategoryID.

I need to write a SELECT query that when given the Name of a ParentCategory will return a list of Items. The ParentCategory table does contain a Name field.

I'm not sure where to begin with this one. I can write a SELECT To return the ParentCategory ID given a category name and I can JOIN the tables.

Thanks!

update 1 -

This works...

SELECT   Item.Name  
FROM         ParentCategory
            INNER JOIN ChildCategory ON ParentCategory.ID = ChildCategory.ParentCategoryID
            INNER JOIN ItemsCategories ON ChildCategory.ID = ItemsCategories.ChildCategoryId
            INNER JOIN Items ON ItemsCategories.ItemId = items.ID
            WHERE ParentCategory.Name = 'XXX'

Upvotes: 0

Views: 60

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460138

Not sure if i've understood your model correctly:

SELECT i.ID,i.Name 
FROM ParentCategory pc 
INNER JOIN ChildCategory cc
ON pc.ID = cc.ParentCategoryID
INNER JOIN ItemsCategories ic
ON cc.ID = ic.ChildCategoryID
INNER JOIN Item i
ON ic.ItemID = i.ID
WHERE pc.Name = @ParentCategory

Upvotes: 1

MarcinJuraszek
MarcinJuraszek

Reputation: 125630

What about

SELECT i.ID
FROM ParentCategory pc
JOIN ChildCategory cc ON cc.ParentCategoryID = pc.ID
JOIN ItemsCategories ic ON ic.ChildCategoryID = cc.ID
JOIN Items i ON ic.ItemID = i.ID
WHERE pc.Name LIKE 'Name'

That should get Items ID, but you can easily extend the SELECT list to contain more data from Items table.

Upvotes: 1

Related Questions