Hierarchy Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hierarchy Question

Author

Topic

chill_uk
Starting Member

5 Posts

Posted - 2012-02-07 : 09:35:59

I have a hierarchy setup to store product categories as below :

ID int
ParentID int
CategoryName varchar(50)

My products are categorised only at the leaf level.

My general category navigation I have got working OK using some CTE queries I found that gives subordinate categories. We show products at every level, with a list that reduces as they drill down - this I achieved just by looking at which products are in the subordinate categories of the current level - i.e. look all the way down at all possible child categories to the lowest level and grab products that match.

However I am now working on the on-site search. When a user searches I want to show them the categories that the products that match their criteria are in, and allow them to drill down within their search results. Ideally I want to start them at level 2 of my hierarchy also.

So when they search they would see the 2nd tier categories of the products that match their criteria, and when they click through they'd see the next tier down and so on.

I can't wrap my head around how to pull out the categories at level 2 to begin with, and then to get the next step down only for the matching products. For my general navigation it's easy as they are all relevant so as long as they are linked it's OK, but in this search scenario they are only relevant if they match the search!

Many Thanks for any light anyone can shed on this!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-07 : 09:40:08

If you can post some sample input data along with the output you want to get in a form that someone can copy and paste, I am sure that many people on this forum would be able to offer you concrete suggestions. Take a look at Brett's blog for some help on how to post it so others can easily use that: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

chill_uk
Starting Member

5 Posts

Posted - 2012-02-07 : 10:22:07

Here goes I hop this helps - I have way oversimplified it but hopefully enough info is here :

My Category table :

CREATE TABLE [dbo].[tblCategory](
	[ID] [int] NOT NULL,
	[ParentID] [int] NULL,
	[Name] [varchar](150) NOT NULL,
 CONSTRAINT [PK_tblCategory] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO tblCategory (ID, ParentID, Name) VALUES (1, 0, 'Power Tools')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (2, 0, 'Hand Tools')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (3, 1, 'Drills')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (4, 1, 'Saws')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (5, 1, 'Planers & Thicknessers')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (6, 3, 'Twist Drills')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (7, 3, 'Hammer Drills')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (8, 5, 'Planers')
INSERT INTO tblCategory (ID, ParentID, Name) VALUES (9, 5, 'Thicknessers')

My Product Table :

CREATE TABLE [dbo].[tblProduct](
	[ProductID] [int] NOT NULL,
	[ProductName] [varchar](150) NOT NULL,
	[CategoryID] [int] NOT NULL
 CONSTRAINT [PK_tblProduct] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (1, 'A Twist Drill', 6)
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (2, 'Another Twist Drill', 6)
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (3, 'A Hammer Drill', 7)
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (4, 'Another Hammer Drill', 7)
INSERT INTO tblProduct (ProductID, ProductName, CategoryID) VALUES (5, 'A Planer with Drill in the name', 9)

This is my CTE that gives me for example all categories under Category ID 1 (Power Tools)

WITH subnodes( Distance, ID, Name, Node_Seq )

AS
( 

SELECT 0,
h.ID,
h.Name, 
CONVERT( varchar(80), ltrim(str(h.ID))) as Node_Seq 
FROM tblCategory h
WHERE h.ID = 1

UNION ALL SELECT distance+1,
h.ID,
h.Name, 
CONVERT( varchar(80), sn.Node_Seq + '.' + ltrim(str(h.ID))) 
FROM tblCategory h 
INNER JOIN subnodes sn ON 
h.ParentID = sn.ID

) 

SELECT Distance, ID, Name, Node_Seq FROM subnodes

So I want to be able to search the product table say for the term "drill" that would return all 5 product records in my listing - but in my summary categorisation I'd ideally want to show initially "Drills" and "Planers & Thicknessers" then if the choose "Drills" it clicks through to show "Electric Drills" and "Hammer Drills" which is the lowest level click through.

So if I feed in search critera and the selected category id, I just want the next level down of categories returned as my results.

I hope that makes sense!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources