Hi,I have a table of products. I have a table of Categories. I need a query that will return a list of categories in order depending on where it is in the categories.So Product 1 could be under: Top - Cat1 - Cat2 - Cat 3 - Prod 1But product 5 could be closer to the top:Top - Cat 1 - Cat 2 - Prod 5Below is some sample data and two tables.Any help is appreciated - Thanks,CREATE TABLE [dbo].[JB_Test_Category_Main]( [CategoryId] [bigint] IDENTITY(1,1) NOT NULL, [CategoryName] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CategoryParentId] [bigint] NULL, CONSTRAINT [PK_JB_Test_Category_Main] PRIMARY KEY CLUSTERED ( [CategoryId] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Top', 0)Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Cat1', 1)Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Cat2', 2)Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Cat3', 3)Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Cat4', 4)Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Cat5', 5)Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Cat6', 6)Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Cat7', 7)Insert [dbo].[JB_Test_Category_Main] (CategoryName, CategoryParentId)Values ('Top', 0)CREATE TABLE [dbo].[JB_Test_Product]( [PID] [int] IDENTITY(1,1) NOT NULL, [CatID] [bigint] NOT NULL, [PName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOInsert dbo.JB_Test_Product (CatID, PName)Values (5, 'Prod 4')Insert dbo.JB_Test_Product (CatID, PName)Values (2, 'Prod 5')Insert dbo.JB_Test_Product (CatID, PName)Values (1, 'Prod 6')Insert dbo.JB_Test_Product (CatID, PName)Values (4, 'Prod 7')/*--Clean Up MessDrop Table dbo.JB_Test_Category_MainGODrop Table dbo.JB_Test_ProductGO*/JBelthoff• Hosts Station is a Professional Asp Hosting Provider• Position SEO can provide your company with SEO Services at an affordable price› As far as myself... I do this for fun!
Query help please?
Posted - 2007-01-19 : 14:19:40
