Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2008
    Posts
    1

    Unanswered: WHERE CONTAINS and multiple fields


    Hi everyone,

    I've run into a road block using WHERE CONTAINS searching on multiple free-text index fields. Here's an example:
    Say you’ve got a table called 'cars'...

    +------+----------------+------------------------+
    | ID | Make | Model |
    +------+----------------+------------------------+
    | 1 | Honda | Accord |
    | 2 | Hyundai | Elantra |
    | 3 | Honda | Civic |
    | 4 | Honda | Fit |
    | 5 | Hyundai | Accent |
    +------+----------------+------------------------+

    And you put a full-text index that includes both Make and Model fields. If you do a SQL query that says:

    SELECT * FROM cars WHERE CONTAINS(*,' "Honda" AND "Accord" ')

    I'd expect record number 1 to be returned - but NO records are returned. I can't use OR because it would then return records 1,3, and 4. I can't figure out how to get just record number 1 while using the free-text syntax. Is there any way to do it? This is for a site-search page that I built, so the actual search terms are coming in from a form, and then are being parsed into individual words to build the WHERE CONTAINS search phrase.

    Thanks very much!

    Rick Towns
    Barrie, Ontario, CANADA

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    A simple answer would be this:

    select * from dbo.Cars where contains (Make, '"Honda"')
    and contains (Model, '"Accord"')

    But I may be missing something.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •