Tricky Hierarchical Search - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Tricky Hierarchical Search

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2001-11-01 : 09:09:01

I have a multi-layered hierarchy.


Categories
 - CategoryID
 - ParentCategoryID
 - Name

This category relates to a bunch of catalog items. My sample category hierarchy is:


Desktop
 - Hardware
    - Servers
    - Workstations
 - Software
    - Server
    - Workstations
Telecom

I am able to write the SQL to search just the category name that each item is associated with, for instance I have multiple items in the Desktop\Hardware\Servers section and multiple items in Desktop\Software\Server section. If I query for 'server' it returns items from both, which is what I want. But what I also want is to be able to query by a parent category name and return all children inside it.

So if I query for Software I get all items from that category and down. I have started something but I'm having a brain lapse as to how to do this.


SELECT
	ci.CatalogItemID,
	ci.Name,
	ci.Description,
	ci.ManufactureID,
	v.Name,
	ci.UnitPrice
FROM
	TESTFINAPP01.witda.dbo.CatalogItems ci
	INNER JOIN TESTFINAPP01.witda.dbo.Vendors v ON ci.VendorID = v.VendorID
	INNER JOIN TESTFINAPP01.witda.dbo.CatalogCategories cc ON ci.CategoryID = cc.CatalogCategoryID
	INNER JOIN TESTFINAPP01.witda.dbo.CatalogStatuses cs ON ci.StatusID = cs.CatalogStatusID AND cs.Name = 'PRIMARY'
WHERE
	ci.Enabled = 1 AND
	(
		ci.EffectiveStartDate IS NULL OR
		ci.EffectiveStartDate <= GETDATE()
	) AND
	(
		ci.EffectiveEndDate IS NULL OR
		ci.EffectiveEndDate >= GETDATE()
	) AND
	(
		ci.CatalogItemID = CASE ISNUMERIC(@criteria) WHEN 1 THEN CONVERT(int, CONVERT(decimal(28, 2), CONVERT(money, @criteria))) ELSE 0 END OR
		ci.UnitPrice = CASE ISNUMERIC(@criteria) WHEN 1 THEN CONVERT(decimal(28, 2), CONVERT(money, @criteria)) ELSE 0 END OR
/**** This is what I was thinking of, I need to join them all together and search both names (I think?). ****/
		cc.CatalogCategoryID IN (SELECT CatalogCategoryID FROM CatalogCategories WHERE Name LIKE '%' + @criteria + '%') OR
/**** ****/
		ci.Name LIKE '%' + @criteria + '%' OR
		ci.Description LIKE '%' + @criteria + '%' OR
		ci.ManufactureID LIKE '%' + @criteria + '%' OR
		v.Name LIKE '%' + @criteria + '%'
	)

One other thing I noticed in SQL Server 7 is that ISNUMERIC may return 1 but CONVERT(int, @criteria) didn't work for decimal values, so I had to cascade it from money (since there may be a value like '$699.00') then to decimal then to int to get it to convert correctly. Any other solution would be much appreciated, perhaps a cast?

All help is much appreciated!

- Onamuji

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources