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

Multipart Identifier??

I am getting an error: Multi-part identifier Ad.IPCode could not be bound.
What does that mean? This is my query:
SELECT Ad.ObjectID, ObjectTypeCode
FROM dbo.ClassifiedAd Ad, dbo.Objects O
JOIN dbo.CommunityProfile CP
ON Ad.IPCode = CP.IPCode AND
CP.StatusCode = 1
WHERE Ad.StatusCode = 1 AND
Ad.Published = 1 AND
Ad.ObjectID = O.ObjectID AND
CONTAINS((SUBJECT, HTMLBody), 'for sale' )Be more axplicit in your FROM clause:
SELECT Ad.ObjectID
,ObjectTypeCode
FROM dbo.ClassifiedAd Ad
inner join dbo.Objects O
on Ad.ObjectID = O.ObjectID
JOIN dbo.CommunityProfile CP
ON Ad.IPCode = CP.IPCode
AND CP.StatusCode = 1
WHERE Ad.StatusCode = 1
AND Ad.Published = 1
AND CONTAINS((SUBJECT, HTMLBody), 'for sale' )
Oh, yes - is there an IPCode yolumn in the dbo.ClassifiedAd table?
If so, then the optimizer might have processed the explicit join (... JOIN
ON ...) before the implicit one (FROM dbo.ClassifiedAd Ad, dbo.Objects O ...
WHERE ...)
ML
http://milambda.blogspot.com/|||Why are you mixing join types (old-style vs. ANSI)? Why do you not use the
alias prefixes on all of your columns? I'll try to re-write this so the
parser understands it, but I have no idea what your table structure looks
like, so I can't fix all the prefixes.
SELECT
Ad.ObjectID,
O.ObjectTypeCode
FROM
dbo.ClassifiedAd Ad
INNER JOIN
dbo.Objects O
ON
Ad.ObjectID = O.ObjectID
INNER JOIN
dbo.CommunityProfile CP
ON
Ad.IPCode = CP.IPCode
AND CP.StatusCode = 1
WHERE
Ad.StatusCode = 1
AND Ad.Published = 1
AND CONTAINS((SUBJECT, HTMLBody), 'for sale' );

>I am getting an error: Multi-part identifier Ad.IPCode could not be bound.
> What does that mean? This is my query:
> SELECT Ad.ObjectID, ObjectTypeCode
> FROM dbo.ClassifiedAd Ad, dbo.Objects O
> JOIN dbo.CommunityProfile CP
> ON Ad.IPCode = CP.IPCode AND
> CP.StatusCode = 1
> WHERE Ad.StatusCode = 1 AND
> Ad.Published = 1 AND
> Ad.ObjectID = O.ObjectID AND
> CONTAINS((SUBJECT, HTMLBody), 'for sale' )|||try this.
Select * from tbl1
where @.searchparam like '%' + email_col + '%'
hope this helps.|||sorry... wrong thread :)
--
"Omnibuzz" wrote:

> try this.
> Select * from tbl1
> where @.searchparam like '%' + email_col + '%'
> hope this helps.
>|||Thanks Aaron. Now I get this: Syntax error near 'sale' in the full-text
search condition 'for sale'.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why are you mixing join types (old-style vs. ANSI)? Why do you not use th
e
> alias prefixes on all of your columns? I'll try to re-write this so the
> parser understands it, but I have no idea what your table structure looks
> like, so I can't fix all the prefixes.
> SELECT
> Ad.ObjectID,
> O.ObjectTypeCode
> FROM
> dbo.ClassifiedAd Ad
> INNER JOIN
> dbo.Objects O
> ON
> Ad.ObjectID = O.ObjectID
> INNER JOIN
> dbo.CommunityProfile CP
> ON
> Ad.IPCode = CP.IPCode
> AND CP.StatusCode = 1
> WHERE
> Ad.StatusCode = 1
> AND Ad.Published = 1
> AND CONTAINS((SUBJECT, HTMLBody), 'for sale' );
>
>
>
>
>
>|||> Thanks Aaron. Now I get this: Syntax error near 'sale' in the full-text
> search condition 'for sale'.
I am not overly familiar with fulltext search, so assumed that syntax was
correct. I would probably write it as a LIKE or PATINDEX condition (again,
still knowing nothing about your table schema, specifically what datatypes
are Subject and HTMLBody, and what table are they in).