Say that users searching a Books database are able to search by Author,
Title, and Publisher using three separate text boxes.  They can choose to
leave any or all text boxes empty if they do not care to filter by that
particular item.

Say I have a query that looks similar to this:

b.* FROM Books b
INNER JOIN CONTAINSTABLE( Books, Author, @author) authorRank ON b.BookId =
authorRank.[KEY]
INNER JOIN CONTAINSTABLE( Books, Title, @title) titleRank ON ( b.BookId =
titleRank.[KEY] )
INNER JOIN CONTAINSTABLE( Books, Publisher, @publisher) publisherRank ON (
b.BookId = publisherRank.[KEY] )

This works great if the user chooses to enter something in each of the three
text boxes.  However, CONTAINSTABLE does not accept an empty string for the
search condition (nor does it accept *, %, or other wildcards except when
searching for a prefix).  How can I get the query to unconditionally match
title and publisher if they leave title and publisher blank but type
something for author?

If there is no way to allow any title and publisher to be returned when the
user leaves those text boxes blank, I will need to write a query for if they
type all three text boxes, a query for if they type in none of the text
boxes, a query if they type in just the publisher but not the title or
author, a query... etc.  Not a good solution.

How can I achieve the results I want without writing multiple queries? 
Thank you.

Hi, Greg

Try something like this (untested):

SELECT * FROM Books
WHERE (@author IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Author, @author)
)) AND (@title IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Title, @title)
)) AND (@publisher IS NULL OR BookId IN (
SELECT KEY FROM CONTAINSTABLE(Books, Publisher, @publisher)
))

Razvan

That doesn't let me use any of the ranking information provided by
CONTAINSTABLE.  It may be workable.  I may have found some sort of
sp_configure setting that will change the behavior of noise words.  I'm not
sure about it yet.

Show quoteHide quote

In this case, you might want to use something like this (also
untested):

SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @author) x ON b.BookID=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @title) y ON b.BookID=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @publisher) z ON
b.BookID=z.[KEY]
WHERE (@author IS NULL OR x.[KEY] IS NOT NULL)
AND (@title IS NULL OR y.[KEY] IS NOT NULL)
AND (@publisher IS NULL OR z.[KEY] IS NOT NULL)

Razvan

If @author is NULL (or blank), then CONTAINSTABLE will throw an error, which
is pretty much the entire problem here.

Show quoteHide quote

Aha! I told you it was untested... :) I assumed that CONTAINSTABLE will
return an empty resultset when given a NULL search condition.
Obviously, I was wrong.

In this case, we can use a non-existent word instead of NULL, like
this:

SET @title=ISNULL(@title,'NotSpecified')
SET @author=ISNULL(@author,'NotSpecified')
SET @publisher=ISNULL(@publisher,'NotSpecified')

SELECT b.*, x.RANK, y.RANK, z.RANK FROM Books b
LEFT JOIN CONTAINSTABLE(Books, Author, @author) x ON b.BookId=x.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Title, @title) y ON b.BookId=y.[KEY]
LEFT JOIN CONTAINSTABLE(Books, Publisher, @publisher) z ON
b.BookId=z.[KEY]
WHERE (@author='NotSpecified' OR x.[KEY] IS NOT NULL)
AND (@title='NotSpecified' OR y.[KEY] IS NOT NULL)
AND (@publisher='NotSpecified' OR z.[KEY] IS NOT NULL)

If you think the word "NotSpecified" may appear in a title of a book,
you can change it with another inexisting word.

Razvan

Order By Primary Key
Select where result are by the best match
where current of cursorName
Really really slow cursor
Problem copying data from column in same table