[thelist] sql server: containstable() with multiple tables?

[rudy]

> Problem there is that the containstable() ranking becomes null
> with anything but an inner join, which I definitely need to display

i see what you're saying

did i mention i had never run into containstable() before?

so i thought i'd better look around a bit, and found a few articles --

  http://www.sql-server-performance.com/tb_search_optimization.asp

  http://www.eggheadcafe.com/articles/20010422.asp

  http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_3syt.asp

gotta love that last one for its typical microsoft mumbo-jumbo like
"CONTAINSTABLE is not recognized as a keyword if the compatibility level is
less than 70. For more information, see sp_dbcmptlevel."

anyhow, since it runs under Indexing Service (formerly "Index Server") and
returns keys to the database query engine, i have no idea how it works, i
simply take it on faith that it does -- love 'em or hate 'em, the microsoft
products i have worked with can usually be made to do exactly what they
claim to be able to do, even if i personally am not capable of making them
do it

a yellow flag i noticed was "Unlike as with regular table indexes, SQL
Server does not update these indexes immediately as data is changed" -- you
gotta run special jobs to populate them

one thing puzzles me -- how come the content field wasn't in your original
query's select list?  i can understand the concept of the ranking (several
of the examples on those sites did things with the rankings, like keyword
weighting, and so on), and i appreciate your remark about the ranking being
null in an outer join, but if you don't select the content, why bother
knowing that the content satisfies the search?  or is your query intended
only to pull up the titles with their related keys so that a subsequent
click will retrieve the content?  that seems to make sense, now that i
think about it...

anyhow, coming back to your problem

you have a containstable query with an inner join that, in effect, serves
simply to qualify which titles you want, along with a rank number

if you wanted to AND another condition which operates on the title, then
you'd use the WHERE that i gave you previously as a qualifier on the INNER
JOIN

however, more probable is that you want titles where the search criteria
were met by the content OR the title (or both)

in this case, you could use a UNION and "fake" the rank

  SELECT t1.id, t1.publishDate, t1.productCode
      , t1.productName, t1.title, t1.author
      , K.rank as Rank
   FROM tips as t1
INNER JOIN containstable(tipContent, content
        , '(""" & searchTerm & """)') as K
     ON t1.id = K.[key]
UNION
  SELECT t1.id, t1.publishDate, t1.productCode
      , t1.productName, t1.title, t1.author
      , -937 as Rank
   FROM tips as t1
WHERE t1.title LIKE [search condition]

if you get a match on title but not on centent, you'll get a row back with
a fake rank of -937 -- pick a number that makes sense to you

notice that you are going to get duplicate titles in the results unless you
choose a fake rank number that you know is always going to come up -- then
the UNION operator will remove the duplicate result row

in summary, containstable() seems to be quite powerful but only if you
really intend to make use of things like weighting and proximity and are
willing to go to all the extra trouble of populating the index server

me, i'd probably wimp out and just make the content a simple large varchar
and use LIKE

;o)


rudy