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

[Chris Blessing]

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

My other issue is that there are several options for this query, including
date ranges and whether the tip is flagged as mailed.  This can make for a
very long and irritating query.

I suppose I could do it though, basically using two large conditions for the
where clause:

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]
WHERE
((<dateRange lower bound condition> and <dateRange upper bound condition>)
and t1.title like '%""" & searchTerm & """%' and t1.title <> 'none' and
t1.isMailed = 'no')
OR
((<dateRange lower bound condition> and <dateRange upper bound condition>)
and t1.title <> 'none' and t1.isMailed = 'no')
ORDER BY blah DESC

This would probably return dupe rows though, since some would match just the
content, others just the title, and still others would match both.  That is
one helluva mouthful. =/

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

> -----Original Message-----
> From: thelist-admin at lists.evolt.org
> [mailto:thelist-admin at lists.evolt.org]On Behalf Of rudy
> Sent: Friday, January 11, 2002 1:50 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] sql server: containstable() with multiple tables?
>
>
> >... but if the tipContent field doesn't contain the search
> > term(s), then that row will be omitted completely won't it?
>
> left outer join
>
> ;o)
>
> rudy
>
>
> --
> For unsubscribe and other options, including
> the Tip Harvester and archive of TheList go to:
> http://lists.evolt.org Workers of the Web, evolt !