Subcategories and categories in mSSQL 2005

[thecfer]

First i will post the Structure of My Table:

SELECT * from categories order by Treedepth

CREATE TABLE [dbo].[categories](
[catID] [bigint] IDENTITY(1,1) NOT NULL,
[parentID] [int] NULL DEFAULT ('0'),
[CatName] [varchar](255) NULL,
[CatLongDesc] [text] NULL,
[Status] [int] NULL DEFAULT ('0'),
[Sortorder] [bigint] NULL DEFAULT ('0'),
[imagepath] [varchar](255) NULL,
[hotornot] [int] NULL DEFAULT ('0'),
[featured] [int] NULL DEFAULT ('0'),
[treedepth] [int] NULL CONSTRAINT [DF_categories_treedepth] DEFAULT ((0)),
PRIMARY KEY CLUSTERED
(
[catID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


Now Tredepth works as

if it has a parentID of 0. Treedepth is 0

If the parentID is 1 or 2 or 3, Treedepth goes for that categories to 1 and if the parentID of any SubSubcategory is 2, which in its own category has parentID of 1 will have a treedepth of 2.

Like this:

Category parentID Treedepth
Main 0 0
Subcat 1(Main) 1
SubSubcat 2(Subcat) 2

Currently, How can i group categories and subcategories to appear under relevant categori or subcategori of their own. Currently they are scattered everywhere.

I hope this make clear

 

[cfSearching]

Can you post your query and output code?

----------------------------------

http://cfsearching.blogspot.com/