I'm using HierarchyID in my ProductCategories table. I then reference the CategoryID in the Products table to determine under what category the product is to be listed. The script for the ProductCategories table is at the end of this message.
Here's what I want to do:
I want to select products from specified category as well as its child categories. This is especially useful if the user ended up in a parent category that doesn't have any products of its own. This generally happens if the category is only being used as
a parent container e.g. Electronics. So if the user ends up in Electronics category, I want to display products from child categories of electronics e.g. MP3 players, digital cameras, etc.
Could someone help me with the SQL for this SELECT statement?
Here's the script for ProductCategories table:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[ProductCategories](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[Category] [varchar](100) NOT NULL,
[CategoryHierarchy] [hierarchyid] NOT NULL,
[StatusID] [tinyint] NOT NULL,
CONSTRAINT [PK_ProductCategories] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
SET ANSI_PADDING OFF
EXEC sys.sp_addextendedproperty @name=
View Complete Post