Search products with parent and child categories

I'm building a shopping cart website and using SQL tables开发者_JAVA技巧

CATEGORY

Id int,
Parent_Id,
Description varchar(100)

Data:

1   0   Electronics
2   0   Furniture
3   1   TVs
4   3   LCD
5   4   40 inches
6   4   42 inches

PRODUCTS

Id int,
Category_Id int
Description...

Data:

1   5   New Samsung 40in LCD TV
2   6   Sony 42in LCD TV

As you can see I only have one column for the last Child Category

Now what I need to do is search by Main Category at homepage, for example if the user clicks to Electronics, show both TVs as they have a Parent-Parent-Parent Id at Electronics, keeping in mind that Products table do have only one column for Category.

Shall I update the Products Table and include 6 columns for category childs in order to solve this? Or how can I build an effective SQL Stored Procedure for this?

Thank you

Jerry

in Oracle, you would use CONNECT BY

If you're using SQL 2008 then you might want to look at the HIERARCHYID data type. Otherwise, you might want to consider redesigning the Category table. How you have it modeled now, you have to use recursion to get from children notes to parents or from parents down through children.

Instead of using the linked list model (which is what you have) you could use the nested set model for hierarchies. Do a search on Joe Celko and Nested Set Model and you should be able to find some good descriptions of it. He also wrote an entire book on modeling trees and hierarchies in SQL. The nested set model requires a bit of set up to maintain the data, but it's much easier to work with when selecting out data. Since your categories will probably remain relatively stable it seems like a good solution.

EDIT: To actually answer your question... you could write a stored procedure that sits in a WHILE loop, selecting children and collecting any products found in a table variable. Check @@ROWCOUNT in each loop and if it's 0 then you've gotten to the end. Then you just select out from your table variable. It's a recursive (and slow) method, which is why this type of a model doesn't work very well in many cases in SQL.

Under almost no circumstances should you just add 6 (or 7 or 8) category IDs to your products table. Bad. Bad. Bad. It will be a maintenance nightmare among other things (what happens when your categories go 7 levels deep... then 8... then 9.

Use recursive CTEs to do this ! works like a dream ! http://msdn.microsoft.com/en-us/library/ms186243.aspx