Multi Level Categories  
Author Message
chrishornuk





PostPosted: Mon Apr 05 19:59:34 CDT 2004 Top

SQL Server Developer >> Multi Level Categories

I have a table that contains categories this table has a relationship to
itself in that some categories may be a sub category of a category.

CREATE TABLE tmpCategories
(CategoryID INT IDENTITY,
ParentCategoryID INT,
CategoryDesc VarChar(20))

INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (Null,
'Savoury')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (1,
'Pies')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (2,
'Meat')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (3,
'Chicken')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (4,
'Vegetable')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (Null,
'Sweet')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (6,
'Cakes')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (7,
'Chocolate')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (7,
'Carrot')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (6,
'Slices')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (6,
'Deserts')
INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (6,
'Buns')

CREATE TABLE tmpProducts
(ProductID INT IDENTITY,
CategoryID INT,
ProductName VarChar(20))

INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (3, 'Shepherds
Pie')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (3, 'Steak Pie')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (4, 'Chicken &
Veg')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (5, 'Vegetarian')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (8, 'Heaven')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (8, 'Dbl Choc')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (8, 'Black Forest')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (10, 'Vanilla
Slice')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (10, 'Chocolate')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (10, 'Lemon Slice')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (11, 'Jelly')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (11, 'Custard')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (12, 'Cream')
INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (12, 'Coffee')


I would like to create a select statement that will return the products that
belong to a supplied CategoryID, for example if I supplied "1" it would
return all savoury products however if I supplied "5" it would only return
vegetarian.

Thanks for any assistance provided


Murphy

SQL Server109  
 
 
Keene





PostPosted: Mon Apr 05 19:59:34 CDT 2004 Top

SQL Server Developer >> Multi Level Categories This may not work for you but if your category level is not that deep maybe
you can create a UDF?


RETURNS int
AS
BEGIN





begin

end
else
begin


else

end

END


then try




select * from tmpProducts




> I have a table that contains categories this table has a relationship to
> itself in that some categories may be a sub category of a category.
>
> CREATE TABLE tmpCategories
> (CategoryID INT IDENTITY,
> ParentCategoryID INT,
> CategoryDesc VarChar(20))
>
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (Null,
> 'Savoury')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (1,
> 'Pies')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (2,
> 'Meat')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (3,
> 'Chicken')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (4,
> 'Vegetable')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (Null,
> 'Sweet')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (6,
> 'Cakes')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (7,
> 'Chocolate')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (7,
> 'Carrot')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (6,
> 'Slices')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (6,
> 'Deserts')
> INSERT INTO tmpCategories (ParentCategoryID, CategoryDesc) VALUES (6,
> 'Buns')
>
> CREATE TABLE tmpProducts
> (ProductID INT IDENTITY,
> CategoryID INT,
> ProductName VarChar(20))
>
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (3, 'Shepherds
> Pie')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (3, 'Steak Pie')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (4, 'Chicken &
> Veg')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (5, 'Vegetarian')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (8, 'Heaven')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (8, 'Dbl Choc')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (8, 'Black
Forest')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (10, 'Vanilla
> Slice')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (10, 'Chocolate')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (10, 'Lemon
Slice')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (11, 'Jelly')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (11, 'Custard')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (12, 'Cream')
> INSERT INTO tmpProducts (CategoryID, ProductName) VALUES (12, 'Coffee')
>
>
> I would like to create a select statement that will return the products
that
> belong to a supplied CategoryID, for example if I supplied "1" it would
> return all savoury products however if I supplied "5" it would only return
> vegetarian.
>
> Thanks for any assistance provided
>
>
> Murphy
>
>


 
 
Joe





PostPosted: Tue Apr 06 10:14:40 CDT 2004 Top

SQL Server Developer >> Multi Level Categories >> .. table that contains categories this table has a relationship to
itself in that some categories may be a sub category of a category. <<

Google the nested sets model; it is much more flexible than the
adjacency list model. Here is the link on Amazon.com for my new book on
"Trees & Hierarchies in SQL"

http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/qid=1080772873/
sr=1-1/ref=sr_1_1/102-7683601-6345721?v=glance&s=books#product-details

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
 
Murphy





PostPosted: Wed Apr 07 17:36:15 CDT 2004 Top

SQL Server Developer >> Multi Level Categories Joe, excellent...

I searched google and found an excellent article you had written for
Intelligent Enterprise that explains everything perfectly:
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=132953

I must admit I had to read it quite a few times until I grasped the concept
but it all makes sense now. In fact your example was so clear I understood
it enough to find a an error in the second SQL statement:

2. Find the employee and all his/her subordinates. (This query has a nice
symmetry with the first query.)
SELECT P2.*
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P2.emp = :myemployee;

This statement should in fact read SELECT P1.* as opposed to P2.* otherwise
it will simply display the row from P2 that is set by the where clause of
the statement repeated.

Once again thanks Joe as nested sets are obviously superior to the adjacency
list model and once understood are quite simple to implement.

Murphy





> >> .. table that contains categories this table has a relationship to
> itself in that some categories may be a sub category of a category. <<
>
> Google the nested sets model; it is much more flexible than the
> adjacency list model. Here is the link on Amazon.com for my new book on
> "Trees & Hierarchies in SQL"
>
> http://www.amazon.com/exec/obidos/tg/detail/-/1558609202/qid=1080772873/
> sr=1-1/ref=sr_1_1/102-7683601-6345721?v=glance&s=books#product-details
>
> --CELKO--
> ===========================
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!