Multiple columns

I'm search on multiple columns useing FREETEXTTABLE, is there a way to know
which column contained the search string? Be low is my query:
SELECT C_ID, ( C_LNAME + ', ' + C_FNAME) as C_NAME, C_SIT1, C_SIT2,
C_SIT3, C_SIT4, C_SIT5, C_SIT6, C_SIT7, C_SIT8, C_SIT9, C_SIT10,
c_city, c_zip, C_hphone, c_wphone, c_cphone, c_email, c_smkcomment,
c_rank_Comment, c_wage_comment, c_edname1, c_edname2, c_edname3,
c_wkemp1, c_wkemp2, c_wkemp3, c_wkcomment, c_refname1, c_refname2,
c_refname3, c_follow, c_comment, k.rank
FROM Candidates as c
inner join FREETEXTTABLE(Candidates, *, '"2 years experience"') as k
on C.C_ID = K.[KEY]
where k.rank > 2
order by k.rank desc
Well you can do something like this, but it does not perform well.
create table Candidates (
C_ID int identity not null constraint CandidatesPK primary key ,
C_LNAME varchar(50),
C_FNAME varchar(50))
insert into candidates (C_LNAME, C_FNAME) values('james', 'bond')
insert into candidates (C_LNAME, C_FNAME) values('james bond', 'test')
insert into candidates (C_LNAME, C_FNAME) values('test','test')
insert into candidates (C_LNAME, C_FNAME) values('test','james bond')
GO
sp_fulltext_database 'enable'
GO
CREATE FULLTEXT CATALOG candidates AS DEFAULT
GO
create fulltext index on candidates(C_LNAME, C_FNAME) key index CandidatesPK
GO
select * from candidates where contains(*,'"james bond"')
declare @.string varchar(20)
set @.string='"James Bond"'
select C_ID, case when a.[KEY] is not null then 'C_LNAME' when b.[KEY] is
not null then 'C_FNAME' end,C_LNAME, C_FNAME from candidates
left join (select * from containstable(candidates, C_LNAME,@.string)) as a on
a.[KEY]=Candidates.C_ID
left join (select * from containstable(candidates, C_FNAME,@.string)) as b on
b.[KEY]=Candidates.C_ID
where a.[KEY] is not null OR b.[KEY ]is not null
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rhogan" <rhogan@.discussions.microsoft.com> wrote in message
news:AC8BBE41-E95C-47B5-BF6A-B5213C830157@.microsoft.com...
> I'm search on multiple columns useing FREETEXTTABLE, is there a way to
> know
> which column contained the search string? Be low is my query:
> SELECT C_ID, ( C_LNAME + ', ' + C_FNAME) as C_NAME, C_SIT1, C_SIT2,
> C_SIT3, C_SIT4, C_SIT5, C_SIT6, C_SIT7, C_SIT8, C_SIT9, C_SIT10,
> c_city, c_zip, C_hphone, c_wphone, c_cphone, c_email, c_smkcomment,
> c_rank_Comment, c_wage_comment, c_edname1, c_edname2, c_edname3,
> c_wkemp1, c_wkemp2, c_wkemp3, c_wkcomment, c_refname1, c_refname2,
> c_refname3, c_follow, c_comment, k.rank
> FROM Candidates as c
> inner join FREETEXTTABLE(Candidates, *, '"2 years experience"') as k
> on C.C_ID = K.[KEY]
> where k.rank > 2
> order by k.rank desc