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

Question

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      honasangregorio@yahoo.com      honasangrego@gmail.com 
156 Qwerty  Qazggf  fgfgf     renrendo2005@yahoo.com.        orendo@gmail.com
184 Xandrick    Flores  NULL      x.flores@gmail.com              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

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.

Answer 3

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