Using Full-Text Search in SQL Server 2005 across multiple tables, columns
- by crisgomez
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]
