I have a table of products that are all categorised. Each product has a categoryID which is a foreign key linked with the Primary Key in the Category table (category.cat_ID).
Each category can also have a parent Category so that a tree like structure is created. The levels deep for this tree is unlimited.
I want to be able to check whether a given product is beneath a given category in the tree, i.e.
If the product was a book on poetic theory, it would be beneath the category if the given category was books, theory or poetic; and not if the category was CDs or DVDs.
My initial thought was to simply create a table variable that gets passed to my procedure as an inout parameter. The process then populates this table with the given categoryID and then all of it's children, and all of their children, etc.
The calling procedure can then use this table to check if the products category ID is present or not.
This is all well and good i think and I could write the code in ColdFusion or C++, but coming to do it in SQL is proving hard on my brain!
I want the procedure to accept 2 things:
@table - the table object that is being populated (inout)
@parentID - the ID of the current category that we are looking for children for (in).
It then must select into the @table all categories that have @parentID as their parent AND for each of these I must call the procedure again (internally looping it), giving the current category as the @parentID.
Does this make sense? And if so, is it possible with SQL code?
Each category can also have a parent Category so that a tree like structure is created. The levels deep for this tree is unlimited.
I want to be able to check whether a given product is beneath a given category in the tree, i.e.
Code:
category tree:
->all
->Books
->theory
->poetic
->CDs
->DVDsIf the product was a book on poetic theory, it would be beneath the category if the given category was books, theory or poetic; and not if the category was CDs or DVDs.
My initial thought was to simply create a table variable that gets passed to my procedure as an inout parameter. The process then populates this table with the given categoryID and then all of it's children, and all of their children, etc.
The calling procedure can then use this table to check if the products category ID is present or not.
This is all well and good i think and I could write the code in ColdFusion or C++, but coming to do it in SQL is proving hard on my brain!
I want the procedure to accept 2 things:
@table - the table object that is being populated (inout)
@parentID - the ID of the current category that we are looking for children for (in).
It then must select into the @table all categories that have @parentID as their parent AND for each of these I must call the procedure again (internally looping it), giving the current category as the @parentID.
Does this make sense? And if so, is it possible with SQL code?
![[banghead]](/data/assets/smilies/banghead.gif "[banghead] [banghead]")
