Asp Forum - Best way to search one table based on another table's data?

Ronald S. Cook

3/31/2007 12:01:00 AM

I have a table of keywords (hundreds/thousands of records):

KeywordID KeywordName
--------- -----------
1 Apple
2 Orange
3 Pear

I then have a table of products (also hundreds/thousands of records):

ProductID ProductName ProductDescription
--------- ----------- ------------------------------------
123 Apple Tree Better than an orange tree, this...
124 Great Scent This great scent smells like orange...

What's the most efficent way to search the entire product table and return
all records that have any of the keywords from the keyword table (in eiter
productname or peoductdescription)?

Thanks,
Ron

6 Answers

--CELKO--

3/31/2007 12:32:00 AM

>> What's the most efficent way to search the entire product table and return all records [sic] that have any of the keywords from the keyword table (in eiter product_name or product_description)? <<

Do not use SQL at all, but get a textbase product that was built for
this kind of work. But here is one kludge skeleton:

SELECT DISTINCT P.product_id
FROM Keywords AS K, Products AS P
WHERE P.product_name LIKE '%'+ K.keyword +'%'
OR P.product_description LIKE '%'+ K.keyword +'%';

Watch out for upper and lower case, unicode, etc.

Tony Rogerson

3/31/2007 7:45:00 AM

> Do not use SQL at all, but get a textbase product that was built for
> this kind of work. But here is one kludge skeleton:

What - Full Text Search? This is part of the SQL Server product and is
completely integrated into T-SQL.

After 35 years in IT - why did you not know this?

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/to... (Ramblings from the field from a
SQL consultant)
http://sqlser... (UK SQL User Community)

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1175301135.055956.45330@o5g2000hsb.googlegroups.com...
>>> What's the most efficent way to search the entire product table and
>>> return all records [sic] that have any of the keywords from the keyword
>>> table (in eiter product_name or product_description)? <<
>
> Do not use SQL at all, but get a textbase product that was built for
> this kind of work. But here is one kludge skeleton:
>
>
> SELECT DISTINCT P.product_id
> FROM Keywords AS K, Products AS P
> WHERE P.product_name LIKE '%'+ K.keyword +'%'
> OR P.product_description LIKE '%'+ K.keyword +'%';
>
> Watch out for upper and lower case, unicode, etc.
>

Tony Rogerson

3/31/2007 7:46:00 AM

Hi Ron,

Check out Full-Text search in books online.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/to... (Ramblings from the field from a
SQL consultant)
http://sqlser... (UK SQL User Community)

"Ronald S. Cook" <rcook@westinis.com> wrote in message
news:eoq6vdycHHA.3632@TK2MSFTNGP02.phx.gbl...
>I have a table of keywords (hundreds/thousands of records):
>
> KeywordID KeywordName
> --------- -----------
> 1 Apple
> 2 Orange
> 3 Pear
>
>
> I then have a table of products (also hundreds/thousands of records):
>
> ProductID ProductName ProductDescription
> --------- ----------- ------------------------------------
> 123 Apple Tree Better than an orange tree, this...
> 124 Great Scent This great scent smells like orange...
>
>
> What's the most efficent way to search the entire product table and return
> all records that have any of the keywords from the keyword table (in eiter
> productname or peoductdescription)?
>
> Thanks,
> Ron
>
>

Ronald S. Cook

3/31/2007 1:17:00 PM

Thanks.. will do.

"Tony Rogerson" <tonyrogerson@torver.net> wrote in message
news:OQD%23Kk2cHHA.4468@TK2MSFTNGP03.phx.gbl...
> Hi Ron,
>
> Check out Full-Text search in books online.
>
> Tony.
>
> --
> Tony Rogerson, SQL Server MVP
> http://sqlblogcasts.com/blogs/to... (Ramblings from the field from
> a SQL consultant)
> http://sqlser... (UK SQL User Community)
>
> "Ronald S. Cook" <rcook@westinis.com> wrote in message
> news:eoq6vdycHHA.3632@TK2MSFTNGP02.phx.gbl...
>>I have a table of keywords (hundreds/thousands of records):
>>
>> KeywordID KeywordName
>> --------- -----------
>> 1 Apple
>> 2 Orange
>> 3 Pear
>>
>>
>> I then have a table of products (also hundreds/thousands of records):
>>
>> ProductID ProductName ProductDescription
>> --------- ----------- ------------------------------------
>> 123 Apple Tree Better than an orange tree, this...
>> 124 Great Scent This great scent smells like orange...
>>
>>
>> What's the most efficent way to search the entire product table and
>> return all records that have any of the keywords from the keyword table
>> (in eiter productname or peoductdescription)?
>>
>> Thanks,
>> Ron
>>
>>
>

--CELKO--

3/31/2007 8:06:00 PM

>> What - Full Text Search? This is part of the SQL Server product and is completely integrated into T-SQL. <<

I also know it "not ready for prime time"; how many document systems
have you implemented? I have one under my belt for the Labor
Relations legal section of a major untility company, consulting for a
company with an FBI contract (not sure if I want to admit that), and
a few much smaller ones.

And it is not well integrated; it is paste-on to an existing storage
model that does not work well with text. Just as a record is not a
row, a row is not a document. There are no semantic net indexes, no
fuzzy operators. The full text columns have to be kept in a form that
can be read by SQL, instead of a compressed form designed for text
searches.

What they have is an early textbase with a Thesaurus (not maintained
by engine, but by the user), "Noise Word" list kept outside of SQL,
and only simple pattern matching, proximity (not adjustable) and
grammatical forms. An occurence computed on word position within a
row ("foobar is word #42 in row #12") and there are much faster ways
to locate things. Oh, and no quorum operators either.

And it also does not follow either SQL conventions or ANSI/ISO Z39
Standards. In fact, the syntax is really weird and hard for non-
programmers to learn. The real coument users know Lexis, Nexis, West
Law and other similar languages.

If it is all you have and you are not doing serious document searching
and you are a programmer and not an end user, then you can probably
get by. For fun, look up ZyIndex which was the "Gold Standard" for
textbases and see the differences. And there are products that beat
it now.

Tony Rogerson

3/31/2007 8:32:00 PM

> I also know it "not ready for prime time"; how many document systems
> have you implemented?

I worked on a team that developed an enterprise Knowledge Management
product, I wrote two major components, one was the dynamic search engine
that incorporated searching Index Server, relational and Full-Text in SQL
Server, all on SQL 2000 (this was 4-5 years ago).

It was for the biggest research department in the UK government - now
QinetQ.

Face it, you haven't even used Full-Text in SQL Server and probably haven't
even read the documentation on how it works or how to use it hence your
incorrectness with the proximity search etc...

Full-Text has its problems and is probably not ready for multi-terrabyte
databases, but, the proportion of people requiring that level is small in
comparison to the general SQL Server user base who mostly have databases
around a few GB.

I refer you back to the OP's question - would you really have them buy a
seperate text search engine that probably does not integrate into SQL Server
would probably cost 10'sK's of $ and require specialist training, knowledge
and support that would be very difficult to come by. The OP is dealling with
a hundreds of thousands of records which is an indicator that their data
isn't TB's but rather GB's.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/to...
[Ramblings from the field from a SQL consultant]
http://sqlser...
[UK SQL User Community]

"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1175371556.794866.151550@y80g2000hsf.googlegroups.com...
>>> What - Full Text Search? This is part of the SQL Server product and is
>>> completely integrated into T-SQL. <<
>
> I also know it "not ready for prime time"; how many document systems
> have you implemented? I have one under my belt for the Labor
> Relations legal section of a major untility company, consulting for a
> company with an FBI contract (not sure if I want to admit that), and
> a few much smaller ones.
>
> And it is not well integrated; it is paste-on to an existing storage
> model that does not work well with text. Just as a record is not a
> row, a row is not a document. There are no semantic net indexes, no
> fuzzy operators. The full text columns have to be kept in a form that
> can be read by SQL, instead of a compressed form designed for text
> searches.
>
> What they have is an early textbase with a Thesaurus (not maintained
> by engine, but by the user), "Noise Word" list kept outside of SQL,
> and only simple pattern matching, proximity (not adjustable) and
> grammatical forms. An occurence computed on word position within a
> row ("foobar is word #42 in row #12") and there are much faster ways
> to locate things. Oh, and no quorum operators either.
>
> And it also does not follow either SQL conventions or ANSI/ISO Z39
> Standards. In fact, the syntax is really weird and hard for non-
> programmers to learn. The real coument users know Lexis, Nexis, West
> Law and other similar languages.
>
> If it is all you have and you are not doing serious document searching
> and you are a programmer and not an end user, then you can probably
> get by. For fun, look up ZyIndex which was the "Gold Standard" for
> textbases and see the differences. And there are products that beat
> it now.
>

microsoft.public.sqlserver.programming

Best way to search one table based on another table's data?

Ronald S. Cook

--CELKO--

Tony Rogerson

Tony Rogerson

Ronald S. Cook

--CELKO--

Tony Rogerson

x Login to ForumsZone