asked on

SQL Server 2008 Thesaurus alternative

I am currently facing a dilemma based on our SQL Server host and our business requirements.

The company would like to establish a hierachy of words in which a person can search.

Example:

If tier one was "web developer" and tier 2 was "ASP.NET" and tier three was "VB.NET" - any searches with web developer will bring results containing ASP.NET and VB.NET. Conversely if you were to search on ASP.NET you would not get web developers but you would get VB.NET programmers.

Our host doesn't allow us to have a XML file of the Thesaurus entries so we cannot use that and the other part of this is that there are many clients in our database. They have to only be able to search their results (which I have covered with a client ID).

Please give me any input on how we can search these tables for skillsets and have the results returned quickly (excellent execution plan) and follow the above rules.

Thanks in advance,

B

lof

Hi,

the question is in SQL Server 2005 and SQL Server 2008 zones. Which version of SQL Server are you using?

lof

You may use Common Table Expression for example.
Have a look at my sample code where I am searching for any parents of a given node.

If you are using SQL 2008 you could use new hierarchical type or if you really want something working as fast as possible, you could have additional table where you would store all the children for a given parent in a flattened way that is regardless the level.
Such table would have two fields:

ParentId, ChildId

with values from my very short example you would have it populated with the following values

ParentId, ChildId
1,2
1,3
2,3

create table keyWords (keyWordId int identity, keyWordParent int, keyWordWord varchar(32))

insert into keyWords values (0, 'web developer')
insert into keyWords values (1, 'ASP.NET')
insert into keyWords values (2, 'VB.NET')

declare @word varchar(32) 
set @word = 'ASP.NET';

with hierarchy as 
(
	select * from keyWords where keyWordWord = @word
	union all 
	select children.* from keyWords children
	inner join hierarchy on children.keyWordParent = hierarchy.keyWordId 
)
select * from hierarchy

Open in new window

ASKER CERTIFIED SOLUTION

lof

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial