Using Full-Text Search in SQL Server 2005 across multiple tables, columns

Question

Hi,

I have a problem, I created a full text search query which return a record(s), in which the paramater I have supplied match(es) in every fields(full-text indexed) of multiple tables. The problem is , when the user.id is equal to ceritification.AId it returns a records eventhough it was not satisfied with the parameter supplied.

For this example I supplied a value "xandrick" which return an Id=184, but the problem is it returns two ids which is 184 and 154.What is the best way to return an ID(s) that satisfied of the supplied given value?

User table
Id  Firstname       Lastname Middlename       Email                      AlternativeEmail
154 Gregorio    Honasan Pimentel      [email protected]      [email protected] 
156 Qwerty  Qazggf  fgfgf     [email protected].        [email protected]
184 Xandrick    Flores  NULL      [email protected]              null

Certification table

Id  AID  Certification                 School
12  184  sdssd                         AMA
13   43  web-based and framework 2     Asian development foundation college
16  184  hjhjhj                        STI
17  184  rrrer                         PUP
18  154  vbvbv                         AMA  

SELECT DISTINCT Users.Id
FROM Users
INNER JOIN Certification on Users.Id=Certification.aid
LEFT JOIN
FREETEXTTABLE (Users,(Firstname,Middlename,Lastname,Email,AlternativeEmail), 'xandrick' )as ftUsr ON Users.Id=ftUsr.[KEY] 
LEFT JOIN
FREETEXTTABLE (Certification,(Certification,School), 'xandrick' )as ftCert ON Certification.Id=ftCert.[KEY] 

Answer 1

SELECT u.Id FROM Users u WHERE FREETEXT(*,'"BPI"') UNION  SELECT c.AId FROM Certification c WHERE FREETEXT(*,'"BPI"') UNION  SELECT ad.AId FROM ApplicantDetails ad WHERE FREETEXT(*,'"BPI"') UNION  SELECT eb.AId FROM EducationalBackground eb WHERE FREETEXT(*,'"BPI"') UNION  SELECT ed.AId FROM EmploymentDetails ed WHERE FREETEXT(*,'"BPI"') UNION  SELECT e.AId FROM Expertise e WHERE FREETEXT(*,'"BPI"') UNION  SELECT ge.AId FROM GeographicalExperience ge WHERE FREETEXT(*,'"BPI"') UNION  SELECT pd.AId FROM ProjectDetails pd WHERE FREETEXT(*,'"BPI"') UNION  SELECT r.AId FROM [References] r WHERE FREETEXT(*,'"BPI"') UNION  SELECT t.AId FROM Training t WHERE FREETEXT(*,'"BPI"')

Answer 2

Another solution but this is very slow compare the below query.

select DISTINCT u.Id  from Users u
 inner join Certification c   on u.ID =
 c.AId inner join ApplicantDetails ad  
 on u.ID=ad.AId inner join 
 EducationalBackground eb   on
 u.ID=eb.AId inner join
 EmploymentDetails ed   on u.Id=ed.AId
 inner join Expertise e   on u.Id=e.AId
 inner join GeographicalExperience ge  
 on u.Id=ge.AId inner join [Language] l
 on u.Id=l.AId inner join
 ProjectDetails pd   on u.Id=pd.AId
 inner join [References] r   on
 u.Id=r.AId inner join Training t  on
 u.Id=t.AId left join FreeTexttable
 (Users,
 (AlternativeEmail,Email,Firstname,Lastname,Middlename),
 'xandrick' ) as uf   on uf.[KEY] =
 u.id left join FreeTexttable
 (ApplicantDetails,
 (Address1,Address2,City,Province,StateorRegion),
 'xandrick' ) as adf   on adf.[KEY] =
 ad.id left join FreeTexttable
 (Certification,
 (Certification,School), 'xandrick' )
 as cf   on cf.[KEY] = c.id left join
 FreeTexttable (EducationalBackground,
 (fieldofStudy,other,School),
 'xandrick' ) as ebf   on ebf.[KEY] =
 eb.id left join FreeTexttable
 (EmploymentDetails,
 (Address1,Address2,City,CompanyName,DescriptionofDuties,Position,Province,TypeofBusiness),
 'xandrick' ) as edf   on edf.[KEY] =
 ed.id left join FreeTexttable
 (Expertise, (Expertise), 'xandrick' )
 as ef   on ef.[KEY] = e.id left join
 FreeTexttable (GeographicalExperience,
 ([Description]), 'xandrick' ) as gef  
 on gef.[KEY] = ge.id left join
 FreeTexttable ([Language],
 ([Language]), 'xandrick' ) as lf   on
 lf.[KEY] = l.id left join
 FreeTexttable (ProjectDetails,
 (Address1,Address2,City,ProjectDescription,Projectname,Projectrole,Province,ServiceRendered,StateorRegion),
 'xandrick' ) as pdf   on pdf.[KEY] =
 pd.id left join FreeTexttable
 ([References],
 (ContactDetails,CurrentPosition,Name,Organization),
 'xandrick' ) as rf   on rf.[KEY] =
 r.id left join FreeTexttable
 (Training, (School,Training),
 'xandrick' ) as tf   on tf.[KEY] =
 t.id

 where uf.[KEY] is not null    OR
 adf.[KEY] is not null   OR cf.[KEY] is
 not null   OR ebf.[KEY] is not null  
 OR edf.[KEY] is not null   OR ef.[KEY]
 is not null   OR gef.[KEY] is not null
 OR lf.[KEY] is not null   OR pdf.[KEY]
 is not null   OR rf.[KEY] is not null 
 OR tf.[KEY] is not null

Answer 3

By far the absolute best way of doing this is to use an indexed view which combines the tables in question. Add your free-text index to the view, then use that for your searches.

Believe it or not, but it is MUCH faster than running multiple freetexttable clauses.