On the design of the table structure of the infinite Classification database, traverse a classification of all product efficiency (two sheets) (a table) __ database

http://q.cnblogs.com/q/39658/

On the design of the table structure of the infinite Classification database, traversing the efficiency of all products under a certain classification

The database table structure is like this:

[Category] Category table

ID (int) Category ID
ParentID (int) parent class ID
name (nvarchar (50)) category names

[Product] Product table

Id (int) Product name
name (nvarchar (50)) Product names
CategoryID (int) Category ID (corresponds to [Category]. ID)
Info (ntext) Introduction information
......

[Category] Category table data
Id ParentID Name

1 0 Garments
2 1 Men's Clothing
3 2 Shirts
4 2 Trousers
5 2 Shorts
6 2 T-shirts
7 2 Coats
8 2 Sweater
9 2 Suits
10 4 Casual Pants
11 4 Trousers
12 4 Jeans
13 4 Sports Pants

[Product] Products table data no longer one by one examples
Id Name CategoryID Info
1
2
3
......

======================================================= traversal is as follows

Id ParentID Name Path

1 0 Garments, 1,
2 1 men, 1, 2,
3 2 shirts, 1,2,3,
4 2 trousers, 1,2,4,
5 2 shorts, 1,2,5,

.....

SELECT * FROM [Product] p where P.categoryid in (select-Id from [Category] C where c.path like (select Path from [Catego RY] where id=2) + '% '

A table:

Trademark Category trademark Category name parent class
1 trademark Big Class 0

2 Trademark Two this Class 1 1
3 Trademark Two This class 2 1
4 Trademark Two This class 3 1
5 Trademark Two This class 4 12 level menu
Select a.ID Commodity category, A.title, b.id level two number from wp_shopcategory A,wp_shopcategory b where a.id=b.pid

Level three Menu
Select a.ID Commodity category, A.title, b.id level two number, c.id three level number, C.title three class name from Wp_shopcategory a,wp_shopcategory B, wp_shopcategory c where a.id=b.pid and b.id = C.pid

http://blog.csdn.net/acmain_chm/article/details/4142971