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

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

Here is one way... I used a join with a like. This will give you one row per matching keyword:

Code Snippet

create table keyword
(
KeywordId int primary key,
KeywordName varchar(10)
)
insert into keyword
select 1,'Apple'
union all
select 2,'Orange'
union all
select 3,'Pear'
go

create table product
(
ProductId int primary key,
ProductName varchar(20),
ProductDescription varchar(40)
)
insert into product
select 123,'Apple Tree','Better than an orange tree, this...'
union all
select 124,'Great Scent','This great scent smells like orange...'
go

select Keyword.KeywordName, Product.ProductName, Product.ProductDescription

from Product
join Keyword
on Product.ProductName like '%' + Keyword.KeywordName + '%'
or Product.ProductDescription like '%' + Keyword.KeywordName + '%'

Which returns:

Code Snippet

KeywordName ProductName ProductDescription

-- -- -

Apple Apple Tree Better than an orange tree, this...

Orange Apple Tree Better than an orange tree, this...

Orange Great Scent This great scent smells like orange...

Or if you don't want duplicates, you could use:

Code Snippet

select Product.ProductName, Product.ProductDescription
from Product
where exists (select *
from keyword
where Product.ProductName like '%' + Keyword.KeywordName + '%'
or Product.ProductDescription like '%' + Keyword.KeywordName + '%')

Another possibility is to use full text search...|||select p.*
from keywords k inner join products p
on p.ProductName like '%' + k.KeywordName + '%'
or p.ProductDescription like '%' + k.KeywordName + '%'