Get what I can call a full intersection

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)

Get what I can call a full intersection

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-08-30 : 06:17:46

Mark writes "I have a rather complex query that I am trying to accomplish for an internal search engine for our intranet. Technically it is not even a search engine but a keyword search system.

The tables:
schWordList - wordID, wordText --- list of words in the search
schWordSearch - wordID, propertyID, attributeID --- associations
PropertyTable - propertyID, propertyCode, otherStuff
PropertyAttributeTable - attributeID, propertyID, otherstuff
sysStringArray - myspid, value, comment

* I am not trying to chicken out by saying other stuff, but they're links and what not else that drives our intranet, which I don't even use for search purposes.

I have a procedure that handels all of the searching (about 5 different apps use searching so I have all the logic in stored procedures). I pass my procedure a list of key words separated by commas (or other delimiter which can be specified), the username, and the property where i"m searching from if needed. I break down the string based on the delimiter and look up the word IDs for each word and put them into sysStringArray with the current @@spid (incase there are multiple searches). I thne have a fairly complex series of joins which takes care of one type of search "find all records with ANY of these words" -- the complexness is to check security for the user and what not; got that part handeled.

What I am trying to do now is get results which have at least all of the words in the sysStringArray (I didn't make the table name, our DBA did and he got it from another site), if there are 5 entries in sysStringArray then each returned property and attributeID pair needs to have all of those 5 wordIDs associated, it can have more but those are the minimum.

I've tried every trick I know and I really don't want to result to nested cursors if I don't have to (in which case this part of the project will be scrapped for being to resource intensive).

thansk
Mark"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-30 : 08:52:10

Try something like this...


Declare @pageKeyWords table (pageId int , wordId int)
Insert Into @pageKeyWords
Select 1,1
Union All Select 1,3
Union All Select 2,2
Union All Select 3,1
Union All Select 3,2
Union All Select 3,3
Union All Select 4,2
Union All Select 4,3


Declare @keyWords table (wordId int identity(1,1), word varchar(100))
Insert Into @keyWords 
Select 'Word1' Union All Select 'Word2' Union All Select 'Word3'


Declare @hasAll varchar(1000)
Set @hasAll = 'Word1,Word2,Word3'

	Select 
		C.PageId
	From dbo.Split(@hasAll,',') as A
	Inner Join @keyWords as B
	On A.data = B.word
	Inner Join @pageKeyWords as C
	On B.wordId = C.wordId
	Group By PageId
	Having count(*) = (Select count(*) From dbo.Split(@hasAll,','))

Set @hasAll = 'Word2,Word3'

	Select 
		C.PageId
	From dbo.Split(@hasAll,',') as A
	Inner Join @keyWords as B
	On A.data = B.word
	Inner Join @pageKeyWords as C
	On B.wordId = C.wordId
	Group By PageId
	Having count(*) = (Select count(*) From dbo.Split(@hasAll,','))

Set @hasAll = 'Word4'

	Select 
		C.PageId
	From dbo.Split(@hasAll,',') as A
	Inner Join @keyWords as B
	On A.data = B.word
	Inner Join @pageKeyWords as C
	On B.wordId = C.wordId
	Group By PageId
	Having count(*) = (Select count(*) From dbo.Split(@hasAll,','))

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources