How to structure a FTS query.

Hi
I'm having some problems structuring my query to execute with relevant
results. Basically I have 1 table that has been FT indexed. The table has a
CountryName, GroupName and ItemName.
A user may enter a query as 'India houshold expenditure'. India is in the
CountryName column and household expenditure in the ItemName colum.
When I use a FREETEXT I end up with all GroupNames and ItemNames that
contain household expenditure. Using CONTAINS produces an error:
Syntax error near 'household' in the full-text search condition 'India
household expenditure'.
Alternatively the user may just enter 'India' or 'household consumption
expenditure'
I am expecting my resultset to be in the case of a query for 'India
household consumption' to show only India where household consumption appears
in the ItemName. Not India and ItemNames where consumption or expenditure
appear individually.
Does anyone have suggestions on how I should go about structuring my
procedure to capture the query cases describe above? Any feedback would be
greatly appreciated.
Regards
Craig
I think what you will have to do is parse your search phrase looking for the
country, perhaps through a look up table. If it detects a country in the
phrase it will issue the following query
select *from tableName where contains(CountryName, 'CountryName') and
(contains(GroupName,'Remainder of search phrase') or
contains(ItemName,'Remainder of search phrase'))
If, you are only interested in ItemName containing the remainder of the
search phrase you could reduce this to
select *from tableName where contains(CountryName, 'CountryName') and
contains(ItemName,'Remainder of search phrase')
"Craig" <Craig@.discussions.microsoft.com> wrote in message
news:BAC003AD-41C6-4B18-B63E-0914BCCF79DB@.microsoft.com...
> Hi
> I'm having some problems structuring my query to execute with relevant
> results. Basically I have 1 table that has been FT indexed. The table
> has a
> CountryName, GroupName and ItemName.
> A user may enter a query as 'India houshold expenditure'. India is in the
> CountryName column and household expenditure in the ItemName colum.
> When I use a FREETEXT I end up with all GroupNames and ItemNames that
> contain household expenditure. Using CONTAINS produces an error:
> Syntax error near 'household' in the full-text search condition 'India
> household expenditure'.
> Alternatively the user may just enter 'India' or 'household consumption
> expenditure'
> I am expecting my resultset to be in the case of a query for 'India
> household consumption' to show only India where household consumption
> appears
> in the ItemName. Not India and ItemNames where consumption or expenditure
> appear individually.
> Does anyone have suggestions on how I should go about structuring my
> procedure to capture the query cases describe above? Any feedback would
> be
> greatly appreciated.
> Regards
> Craig