sub queries in full text searches

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 2005 Forums

Transact-SQL (2005)

sub queries in full text searches

Author

Topic

javedwahid
Starting Member

5 Posts

Posted - 2009-11-17 : 20:27:23

Hi,

I just created my first full text catalog and am trying to see how to create queries on it.
I have 2 tables like these:


Books:

ID	Title									            Date
---	---------------------------------------------------------------	-----------
1	Microsoft .NET Framework 2.0 Application Development Foundation   2009-11-17

2	Self paced trainging guide for your first certification		2008-12-31

3	Programming in Visual Basic .NET, 2005 Edition				2009-07-02

4	ActionScript 3.0 Game Programming University for beginners		2007-01-14

5	Windows Server 2008 Tomorrow's Technology, Today!			2010-01-01


SearchTerms:

ID	Word
---	---------------
1	Microsoft

2	Programming

3	Javascript

regularly I would use this type of concept:


SELECT *
FROM Books
WHERE Title IN ( SELECT Word
		       FROM SearchTerms )

THIS OBVIOUSLY DOESN'T WORK HERE AND WOULDN'T RETURN ANY RESULTS

So I thought that I would try something like this:


SELECT *
FROM Books	B
WHERE CONTAINS(Title, ( SELECT Word 
                          FROM SearchTerms ) )

Hoping that it would return records 1,3,4 - but it didn't, I just got an error

Does anyone know how to query something like this, I wasn't able to find anything on using sub queries with the CONTAINS keyword

prakum
Starting Member

16 Posts

Posted - 2009-11-18 : 00:34:05

hi...

check this out...i have done using cursor.....you can put this inside a procedure also....

CREATE TABLE #temp (ID int,Title varchar(max),Date datetime)
DECLARE @tmp varchar(50)
DECLARE vendor_cursor CURSOR FOR
SELECT Word
FROM SearchTerms
OPEN vendor_cursor
FETCH NEXT FROM vendor_cursor
INTO @tmp

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #temp
SELECT ID,Title,Date from Books
WHERE CONTAINS(Title, @tmp)
FETCH NEXT FROM vendor_cursor
INTO @tmp

END
SELECT * from #temp
close vendor_cursor
drop table #temp
deallocate vendor_cursor

Praveen Kumar

javedwahid
Starting Member

5 Posts

Posted - 2009-12-09 : 14:52:51

Sorry for the very late reply.

Thanks for the info Praveen, I didn't have a chance to test this on my database, supervisor decided to go with another solution, kinda just cut it out, but i'll definitely keep the info, because we're going to come back to this later.

Do you think this will have any performance issues when comparing like 100000 words against maybe like a couple million records?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources