Post reply

Full Text Search Join

  • April 8, 2010 at 1:23 pm

    #222939

    I have create a full text index on a table Addresses on the column Address1. I have created a second table SearchWords with the field Words. The Words column contains the words I want to search for in the Addresses column. I tried something like this below but it does not work. Are there any suggestion?

    Thanks!

    SELECT Address_1

    FROM Addresses ad

    JOIN SearchWords sw ON CONTAINS(ad.Address_1, sw.Words))

  • April 8, 2010 at 1:52 pm

    #1148633

    what do u mean by doesnt work. does it error? Or just doesnt return results. Is this full text index newly created?

  • April 8, 2010 at 2:02 pm

    #1148640

    the reason i ask because the full text index probably just hasnt populated. their tricky when u set them up sometimes. If it hasnt populated then it wont return anything

  • April 8, 2010 at 2:15 pm

    #1148643

    It returns incorrect syntax. I believe it is populated because the following works fine.

    SELECT Address_1

    FROM Addresses ad

    WHERE CONTAINS(ad.Address_1, 'bike')

  • April 8, 2010 at 2:38 pm

    #1148654

    i see what ur doing wrong. you can join on that. it has to be something like this however i need the talbes columns to give a good solution

    SELECT Address_1

    FROM Addresses ad

    JOIN SearchWords sw

    ON --u need to join on something valid here

    where CONTAINS(ad.Address_1, sw.Words))

    How many records are in the search words table and what are the columns in both tables?

  • April 8, 2010 at 2:40 pm

    #1148656

    sorry i meant u CAN'T join on that

  • April 8, 2010 at 2:46 pm

    #1148663

    For the sake of this example Addresses has 1 column, Address1, and SearchWords has 1 column, Words. I want to use the Words column to to do a full text search in the Address1 column instead of querying each word individually.

  • April 13, 2010 at 1:56 pm

    #1150999

    i'm not accustom to joining tables w/o something in both tables having something related.

    you may have to just loop. Also i would suggest sending your table structures and some sample inserts. trust me this would have been answered days ago. Most of these guys wont even look at it if there isnt enough info in there.

  • April 13, 2010 at 2:05 pm

    #1151006

    Thanks for the suggestion. I solved it by using a table valued function and a cross apply with table that had the values I wanted searched.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply