Contains(*) question

When I do a full text index on 2 columns, then do a query like below, it
appears to only match rows where 1 column of the index satisfies the
criteria. I want the query to return all rows where a combination of the 2
columns satisfy the query. Do I have something set up wrong?
SELECT * FROM <table>
WHERE CONTAINS(*,'"lord","rings","dvd"')
For the following data, no row is returned, but I want it to be
column 1 contains 'lord' and 'rings'
column 2 contains 'dvd'
For the following data, a row is returned.
column 1 contains 'lord' and 'rings' and 'dvd'How about :
SELECT * FORM <table>
WHERE CONTAINS(*, '"lord" OR "rings" OR "dvd"')
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Brian Kitt wrote:

>When I do a full text index on 2 columns, then do a query like below, it
>appears to only match rows where 1 column of the index satisfies the
>criteria. I want the query to return all rows where a combination of the 2
>columns satisfy the query. Do I have something set up wrong?
>SELECT * FROM <table>
>WHERE CONTAINS(*,'"lord","rings","dvd"')
>For the following data, no row is returned, but I want it to be
>column 1 contains 'lord' and 'rings'
>column 2 contains 'dvd'
>For the following data, a row is returned.
>column 1 contains 'lord' and 'rings' and 'dvd'
>
>|||But I need the results to contain all 3 terms. An 'or' would return results
that contain 1 of the 3.
"Mike Hodgson" wrote:

> How about :
> SELECT * FORM <table>
> WHERE CONTAINS(*, '"lord" OR "rings" OR "dvd"')
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Brian Kitt wrote:
>
>|||On Sun, 9 Oct 2005 19:17:01 -0700, Brian Kitt wrote:

>When I do a full text index on 2 columns, then do a query like below, it
>appears to only match rows where 1 column of the index satisfies the
>criteria. I want the query to return all rows where a combination of the 2
>columns satisfy the query. Do I have something set up wrong?
>SELECT * FROM <table>
>WHERE CONTAINS(*,'"lord","rings","dvd"')
>For the following data, no row is returned, but I want it to be
>column 1 contains 'lord' and 'rings'
>column 2 contains 'dvd'
>For the following data, a row is returned.
>column 1 contains 'lord' and 'rings' and 'dvd'
Hi Brian,
I don't know much about full text indexing, so this one is a shot in the
dark - but would this work?
SELECT col01, col02, ...
FROM YourTable
WHERE CONTAINS (*, '"lord" AND "rings"')
AND CONTAINS (*, '"dvd"')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Sorry, I misunderstood what you were trying to achieve. It seems like
the media type ought to be in its own column and referenced with normal
string operators rather than the CONTAINS() predicate. Something like:
select MediaTitle, MediaType, ... from Media
where CONTAINS (MediaTitle, '"lord" AND "rings"')
and MediaType = "dvd"
With a nonclustered index on the MediaType column, that would work much
more efficiently than a couple full-text searches. If you cannot change
the design then Hugo's suggestion looks like it should work, but that's
a really poor design (just having all the metadata jumbled together like
that) - you may as well just have a bunch of text files containing the
search terms in a directory structure and use the Windows explorer
search function to trawl through the text files. Why store data in a
relational database if it's not relational data?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Brian Kitt wrote:
>But I need the results to contain all 3 terms. An 'or' would return result
s
>that contain 1 of the 3.
>"Mike Hodgson" wrote:
>
>|||Brian,
This is a FAQ in the fulltext newsgroup, so I've blogged about how to do FTS
across columns - "SQL Server FTS across multiple tables or columns" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!316.e
ntry
Enjoy,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message
news:OYUovffzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Sorry, I misunderstood what you were trying to achieve. It seems like
> the media type ought to be in its own column and referenced with normal
> string operators rather than the CONTAINS() predicate. Something like:
> select MediaTitle, MediaType, ... from Media
> where CONTAINS (MediaTitle, '"lord" AND "rings"')
> and MediaType = "dvd"
> With a nonclustered index on the MediaType column, that would work much
> more efficiently than a couple full-text searches. If you cannot change
> the design then Hugo's suggestion looks like it should work, but that's
> a really poor design (just having all the metadata jumbled together like
> that) - you may as well just have a bunch of text files containing the
> search terms in a directory structure and use the Windows explorer
> search function to trawl through the text files. Why store data in a
> relational database if it's not relational data?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Brian Kitt wrote:
>
>