Using FREETEXT and INNER JOIN - 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 2008 Forums

Transact-SQL (2008)

Using FREETEXT and INNER JOIN

Author

Topic

CraigMuckleston
Starting Member

6 Posts

Posted - 2009-07-20 : 08:43:07

I am trying to use full text index. I have a basic query that runs fine and all is well. I want to modify this query to use an inner join, but get an error:

A CONTAINS or FREETEXT predicate can only operate on one table or indexed view. Qualify the use of * with a table or indexed view name.

On the freetext line. My query is as follows:

select f.ID as 'GUID',
f.intMemberID as 'MemberID',
f.strTitle AS 'Title',
f.dtmCreateDate AS 'Date Created',
f.dtmExpiryDate AS 'Expiry Date',
f.strDescription AS 'Description'
from tblTest f (nolock)
inner join tblMembers m on m.intID = f.intMemberID
where freetext(*, @strSearchString)
and f.bolComplete = 0
and f.dtmExpiryDate >= getdate()
and m.strPostCode = @strPostCode
order by f.dtmExpiryDate asc

any ideas how I can get around this?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-20 : 08:59:43

Replace * with m.* or f.* depends on which table you want to search for

Madhivanan

Failing to plan is Planning to fail