this works
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John') and contains (*, 'Smith')
this doesn't
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, 'John AND Smith')
i believe this doesn't work because it is searching each individual column
(firstname and lastname) for both john and smith. there are no rows that have
both john and smith in either the firstname column only or the lastname column
only. same thing as this
SELECT UID, FirstName, LastName FROM Users
WHERE CONTAINS (*, '"john smith"')
the or works because it finds rows with john or smith in the firstname column
and then finds rows with john or smith in the lastname column.
[quoted text, click to view] Andrew Jocelyn wrote:
> Hi
>
> Can someone tell me why I can search multiple columns in 1 table using a
> single CONTAINS with 'OR' operator but it dosn't work for 'AND' queries? I
> want to be able to do 'AND' searches accross 50 odd columns in a table.
> Here's an example to show you what I mean (A Full-Text index is working on
> the table and includes the FirstName and LastName columns):
>
> CREATE TABLE Users
> (
> UID int IDENTITY(1000,1) PRIMARY KEY,
> FirstName varchar(50) DEFAULT '' NOT NULL,
> LastName varchar(50) DEFAULT '' NOT NULL
> )
>
> INSERT INTO Users(FirstName, LastName) VALUES('John', 'Smith')
> INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Smith')
> INSERT INTO Users(FirstName, LastName) VALUES('John', 'Brown')
> INSERT INTO Users(FirstName, LastName) VALUES('Bob', 'Brown')
>
> SELECT UID, FirstName, LastName FROM Users
> WHERE CONTAINS (*, 'John OR Smith')
>
> /* The above gives me the resulst I expect, i.e. 2 records are returned*/
>
> SELECT UID, FirstName, LastName FROM Users
> WHERE CONTAINS (*, 'John AND Smith')
>
> /* This one dosn't return any results even though 'John' and 'Smith' clearly
> exists in 1 record but different columns */
>
> Please tell me what I'm doing wrong or what a pracical solution would be to
> search 50ish columns in a table using 'AND' operator and 1 CONTAINS
> pedicate. I can't find a suitable explaination in BOL
>
> Many thanks
> Andrew
